Hi All,
Hopefully this is doable but I'm drawing a blank on displaying actual and budget numbers on a pivot table.
So here's my scenario:
I have a pivot table with a dimension(account) and two measures(Amount(YTD) and Amount(FD)), the requirement is to show sum of actual for past accounting periods and sum of budget for future accounting periods.
Plotting accounts vs both measures gives a pivot table with $0.00 columns and data handling option doesn't seem to work, Is there a way to suppress the zero columns?
Both measures are calculated as;
Amount(YTD Actual): Sum({<[Acc Period]={"<$(vCurrentMonth)"},Source={"Actual"}>}[Actual Amount])
Amount(FD Budget): Sum({<[Acc Period]={">=$(vCurrentMonth)"},Source={"Budget"}>}[Budget Amount])
I've also tried creating a calculated dimension to show only the applicable accounting periods but it's not giving expected results either.
Requirement;
if accounting period < current month, sum(Actual Amount)
if accounting period >= current month, sum(budget amount)
Please advice.
Thank you
