Sorting month axis by last available month

[AdrianSantos___]

Hello everyone,

I work with a shared PowerBI dataset with read-only permission, therefore I cannot edit or format any fields. The dataset does not have a dimensional table for date, all dates are fields within the fact table. Dates are formatted as Date/Time therefore will show up with either as a hierachy (4 fields) or a full date with time included.

I need to build a graph that will show me data from the last 12 months but I want it to be dynamic. For instance, if I build the report in April I want the data to show me April, March, February, etc. in a moving annual total perspective.

The problem is that if I build a graph with a date hierarchy and drill to Month, it will always order the months from Jan to Dec. In the example below I have data until 31st of March, but the graph will always order it with January first.

Is there a way to order the axis in terms of the latest available month? I believe that even if I had a column with Month only it would not automatically sort.

Thank you !

[tackytechtom · Accepted solution]

Hi @AdrianSantos___ ,

How about creating a SortOrder measure which you can use a tooltip to sort your graph on:

MeasureOrderMonth = 
VAR _currentMonth = MONTH ( TODAY() )
VAR _dataMonth = MONTH ( MAX ( Table[Date] ) )
RETURN
IF ( _currentMonth  >= _dataMonth,  (_dataMonth  - _currentMonth + 12) * 10, _dataMonth ) 

If the current month was April, _currentMonth would be 4. 

If the data was April, _dataMonth would be 4.

--> MeasureOrderMonth returns 120

If the current month was April, _currentMonth would be 4. 

If the data was March, _dataMonth would be 4.

--> MeasureOrderMonth returns 110

If the current month was April, _currentMonth would be 4. 

If the data was February, _dataMonth would be 2.

--> MeasureOrderMonth returns 100

If the current month was April, _currentMonth would be 4. 

If the data was January, _dataMonth would be 1.

--> MeasureOrderMonth returns 90

If the current month was April, _currentMonth would be 4. 

If the data was December, _dataMonth would be 12.

--> MeasureOrderMonth returns 12

If the current month was April, _currentMonth would be 4. 

If the data was November, _dataMonth would be 11.

--> MeasureOrderMonth returns 11

etc.

Does this work for you? 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/