12 month rolling and last year 12 month rolling, completed months, financial/custom years

[awo]

I am very new to Power BI. Currently I am struggling with calculation of rolling Sales amounts.

If I want to calculate it today, then it is Sept 2015 - Aug 2016 for LY 12 months and Sept 2016 - Aug 2017 for CY 12 month rolling.

In one table I have (FactTable) Sales amounts monthly + many other dimensions. In another I created Calendar with almost all possible month names, numbers, quarters, year (Financial years are 15/16, 16,17) etc, all what I might need for visuals. I dont have anything related to days. I am going to refresh FactTable every month with new completed month. So there is no need to check if month is completed.

Fact table includes also historical data back to 2011. Calendar table is set to 2020.

The Index which connects both table is in this format, i.e. 15/16January, 16/17March.

Unfortunately none of the DAX expressions I used to calculate those measures gave me correct amounts. I got very odd looking results.

Could you please help me??

I went through all topics related to rolling periods.

Thank you in advance!

[Himanshu]

Hi awo ,

For Last Year Rolling Use ::

Amount running total 2016 =
Calculate([Amount Sum],
SAMEPERIODLASTYEAR('Date'[Date].[Date])
)

For Max Year Rolling Sum Use :

CALCULATE(
SUM('Data'[Amount]),
FILTER(
ALLSELECTED('Date'[Date]),
ISONORAFTER('Date'[Date], MAX('Date'[Date]), DESC)
)
)

[awo]

Thank you Himanshu for quick reply!! 🙂

Your DAX works! However I dont know what periods/how to set them/ to use in rows or columns. If I use fiscal year, it shows me amounts YTD: October - August. If I change it to 2016, it shows me amount for January - December.

Max Year Rolling Sum shows exactly the same results.

[Himanshu]

Period cycle is Jan-Dec period for 2016,2015... and for current year for starting date to Max(Date)of 2017.

Thanks, 

[awo]

My 12 month rolling period is Jan-Dec only every 12 months, so once a year. How can I visualize rolled amounts for periods like, i.e.

Sep 2015 - Aug 2016, Sep 2016 - Aug 2017.