Summarizing multiple time periods in one table view - Financial

[Luke Williams]

I have a cross table with multiple calculated columns that would show up like a normal financial view ie; Actual-Month, Actual FYTD, Actual vs Budget etc.

As I have values in the table for budget and forecast that go to the end of the current financial year, I cannot work out what expression to use on the x-axis that would allow users when looking at for example; December 19's actuals vs December budget (monthly comparison), Actual FYTD vs Budget FYTD and also Full year budget vs full year forecast.

The current expressions I am using are below.

Sum([Actual]) then Sum([Value]) over (Intersect(AllPrevious([Axis.Columns]),NavigatePeriod([Axis.Columns],"Year",0,0))) as [Actual FYTD],

Sum([budget]) then Sum([Value]) over (Intersect(AllPrevious([Axis.Columns]),NavigatePeriod([Axis.Columns],"Year",0,0))) as [budget FYTD],

Sum([Forecast]) then Sum([Value]) over (Intersect(AllPrevious([Axis.Columns]),NavigatePeriod([Axis.Columns],"Year",0,0))) as [Forecast FYTD],

Sum([Actual] - [budget]) then Sum([Value]) over (Intersect(AllPrevious([Axis.Columns]),NavigatePeriod([Axis.Columns],"Year",0,0))) as [Act.vs.Bgt Variance - FYTD],

Sum([Actual]) over (Intersect(AllPrevious([Axis.Columns]),NavigatePeriod([Axis.Columns],"Year",0,0)))

/

Sum([budget]) over (Intersect(AllPrevious([Axis.Columns]),NavigatePeriod([Axis.Columns],"Year",0,0))) as [Act.vs.Bgt Variance - FYTD %],

Sum([Actual]) over (Intersect(AllPrevious([Axis.Columns]),NavigatePeriod([Axis.Columns],"Year",0,0)))

-

Sum([Forecast]) over (Intersect(AllPrevious([Axis.Columns]),NavigatePeriod([Axis.Columns],"Year",0,0))) as [Act.vs.Fcst Variance - FYTD],

Sum([budget]) over (Intersect(AllPrevious([Axis.Columns]),NavigatePeriod([Axis.Columns],"Year",0,0)))

-

Sum([Forecast]) over (Intersect(AllPrevious([Axis.Columns]),NavigatePeriod([Axis.Columns],"Year",0,0))) as [bgt.vs.Forecast - Full Year]

 

Unfortunately, for privacy reasons, I cannot upload a sample .dxp.

[Luke Williams]

So it appears I've found one solution to this.

At the beginning on my expressions I've added a SUM(if()) clause to take the current month and subtract the number of months to the last data point.

So my Actual for the month looks like this;

Sum(If(Date([Calendar Date])