cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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

Dax Formula for Previous 12 MonthsDax Formula for Previous 12 MonthsGraph outcomeGraph outcome

My final hurdle is that iIm trying to get in to sort in order of last 12 Month. That image is filtered on 30/9/19 I would like it to be far right and then go backwards eg Aug 19, July 19, Jun19... to Oct18.

Adding report time to Column Series I was able to split the years but no further.

Any help would be greatly appreciated. 

Thanks in advance.

Eamon

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User II
Super User II

Re: Rolling 12M Date Order based on Selection

Hi @EamG13 
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. 






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa

View solution in original post

5 REPLIES 5
Highlighted
Super User II
Super User II

Re: Rolling 12M Date Order based on Selection

Hi @EamG13 ,

 

After several testing, I realized that there was no way to sort the period chronologically if i just use month as the axis. The workaround is to include both year and month from the date hierarchy then sort the visual by YearMonth. Turn off concatenate labels. The Years will still be visible but you may cover it with a shape then just group with the bar chart.






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa
Highlighted
Frequent Visitor

Re: Rolling 12M Date Order based on Selection

Hi @danextian,

 

Thanks for the quick reply. I have turned off concatenate labels and have the date hireachy detup with Year and month but still shows in the same format unfortunatley. I'll keep playing around with it.

 

Are there any other simpler formuls that would just simply show last 12 months absed on date/month selection? 

 

Thanks

Highlighted
Super User II
Super User II

Re: Rolling 12M Date Order based on Selection

Hi @EamG13 
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. 






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa

View solution in original post

Highlighted
Super User IX
Super User IX

Re: Rolling 12M Date Order based on Selection

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/



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Frequent Visitor

Re: Rolling 12M Date Order based on Selection

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

Helpful resources

Announcements
Meet the 2020 Season 2 Power BI Super Users!

Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors