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

[awo]

Hello,

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.

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?

Thank you in advance!!!

[Ashish_Mathur]

Hi,

To use Date/Time intelligence functions of the PowerPivot, you will need to create a relationship between the Date column of both datasets.

[CahabaData]

First, at the table view - sanity check that your date tables are actually date field types, rather than text.  This is managed by highlighting the column and up in the ribbon, modeling, is data type selector.  Imported data is sometimes misunderstood by PBI.  This could explain the issues you are having.

Otherwise it sounds like you need Year To Date (YTD) function plus Prior Year function.  There is alot of Q&A on these and tutorials.  

If still no success then post a short sample of your raw data starting table - and a short sample of your goal result.