Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I want to display a bar chart with Trailing 12 months (TTM) using a Relative Date slicer with Last 12 months. The issue is that my last 12 months don't sort correctly. If we're in Sept 2018, I'd like the the first bar to be Oct 2017, then Nov 2017, and go all the way to Sept 2018. But it doesn't happen like that as the bars sort alphabetically or by month number (I have a column month number also).
I also tried to Sort by 'Month number overall' which I also have in my table. But it throws an error because my Month Names have more than one Month Number Overall. That is, Jan is 171, and also 183, and 195 etc.
I'm looking for an easy solution using the Relative Date Slicer. Please advise.
Hi,
I've managed to solve this for the data I have in the current month, but have yet to test the formula in the next month to see if it's working properly.
So I have a calendar table with the following structure:
1st day of month | Year | Month Number | Month Short Name |
01-01-2020 | 2020 | 01 | Jan |
... | ... | ... | ... |
Then I've added a custom column with the following code:
Month Sort =
VAR __rowdate = 'Calendar Table'[1st day of month]
VAR __lastdate =
CALCULATE (
LASTDATE ( 'Calendar Table'[1st day of month] ),
ALL ( 'Calendar Table' )
)
VAR diffY =
DATEDIFF ( __rowdate, __lastdate, YEAR )
VAR diffM =
DATEDIFF ( __rowdate, __lastdate, MONTH ) + 1
VAR result =
IF (
diffM > 12
&& MOD ( diffM, 12 ) <> 0,
MOD ( diffM, 12 ),
IF (
diffM > 12
&& MOD ( diffM, 12 ) = 0,
diffM / diffY,
diffM
)
)
RETURN
result
Then you sort Month Short name by this new column.
The idea is to use MOD function to remain in the range 1...12 once the increment for relative month number (diffM) passes 12.
In your case you would probably change __lastdate to your current slicer selection and then work with n months prior to it.
Maybe not an ideal solution, but my graphs work alright (for the moment).
Let me know if it works for you.
Cheers,
Hi,
Was a solution found for this? I have exactly the same problem. I had solved it using a month sort but if the user selects a different begining month, the month name is sorted incorrectly.
cheers
Emil
Hi,
See if my solution here helps - Flex a Pivot Table to show data for x months ended a certain user defined month.
Hi,
This is what you should be doing
Hope this helps.
Hi Ashish,
Thanks for your response. Unformtunately, this does not produce the desired results. This will sort the months as Jan, Feb, Mar........., Nov, Dec.
I wish to sort them based on last 12 months from the current month. So if we're in Sept 2018, I would like the chart to display 12 bars, starting left to right from Sept 2017, Oct 2017, Nov, 2017..........Jun 2018, Jul 2018, Aug 2018. Thanks.
Hi,
Perhaps i need to see your data and expected result.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |