PowerPivot DAX Rolling 13 month finance report with a single YTD column

[DaveBlakeMAAT]

Hello

I have a PowerPivot data model that lists cost centres on the rows and accounting periods on the columns, what I need is a DAX formula that will return a single YTD sum column, currently if i create a YTD Measure and add it to my Pivot I get a column for each accounting period, i want to restrict it so that it only shows the YTD for the most recent accounting period.

in essence I want to acheive the following (note there will be additional columns such as variance analysis).

I am hoping that someone may be able to point me in the right direction, or tell me that it is not possible.

regards

Dave

 

[Matt Allington]

You need to reshape your data. Unpivot the dates so you have a period column and a value column. You can do that in power query.
Then you should get a calendar table. Power Pivot Calendar Tables - Excelerator BI

then you should read my article about time intelligence. DAX Time Intelligence Explained - Excelerator BI

I hope that helps