Jump to content

Summarizing multiple time periods in one table view - Financial

Luke Williams

Recommended Posts

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.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...