Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Rolling 12M Date Order based on Selection

Hi, Im a new user to Power BI and have been trying to design my reports. I have looked up previous Rolling 12 Month questions and was able to source the below Dax statement My final hurd...
Need Help
  • danextian's avatar
    danextian
    6 years ago

    Hi Anonymous 
    Did you want to show the period as "MMM YY"? If so, you can create a custom column for that 

    Month and Year = 
FORMAT ( Table[Date], "MMM YY" )
 
Month and Year Sort= 
FORMAT ( Table[Date], "YYYYMM" )

    The second formula above is for sorting the first formula. Once you have created these calculated columns, select Month and Year, go to Column Tools and Sort by Month and Year Sort. 

amitchandak's avatar
amitchandak
Super User
6 years ago

Any formula you apply needs that sorting need to corrected. So you have to create the moth-year and month year sort as suggested above.

You can use the following for rolling, but make sure you have date calendar

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH)) 
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),-12,MONTH))

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi amitchandak & danextian 

Would like to thank you very much. Danextian's 2nd comment solution worked to sort in the order I required.

The blog link was also very helpful.


Thanks

Eam