Forum Discussion
Helper IITrailing 12 months, year over year
Hi StaceyGriffeth ,
Sorry for late reply. Thanks for your reply.😊
It's quite easy. You just need to find your "Relative Month" column in Table "Calendar" and drag it to the "tooltips" field. Then sort the chart by "Relative Month". (ascending)
Hope that's what you were looking for.
Best Regards,
Yuna
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I guess you could merge both my solution and Yuna's solution together:
Create both the numerical and text monthYear fields in your calendar and set the text field sort order based on the numerical field. Then place the text field into the tooltip instead of on the axis then sort on it in the chart.
This would add the monthYear text to the tooltip, but it wouldn't be as offensive/confusing as having the relative month number on there.
Pete
Helper IIAnonymous that's it! The only change is I would have must recent month on the right, so just reverse order of what you have. Can you share the revised .pbix with me so I can see how you did it? Clearly I just wasn't understanding how BA_Pete advised to do it.
Helper IIAnonymous would you share with me how you got the months to fall in that order? This is exactly what I need to do.
- BA_Pete
Super User
Hi StaceyGriffeth ,
Apologies for the late reply, Easter break 🙂
You can do what Anonymous suggests below or, if you don't want the relative month to show in your tooltip, you will need to set up a couple of new columns in your calendar:
- A numerical month/year column to sort on, and
- A textual month/year column to display in your axis
As your axis spans years, we need to include the year in the label and sort key so PBI knows how to sort correctly.
Numerical:
Power Query M
monthYearKey = Date.Year([Date]) * 100 + Date.Month([Date])DAX
monthYearKey = YEAR(calendar[Date]) * 100 + MONTH(calendar[Date])Textual:
Power Query M
monthYear = Text.Combine( { Text.Start(Date.MonthName([Date]),3), Text.End(Text.From(Date.Year([Date])),2) }, " " )DAX
monthYear = LEFT(FORMAT(MONTH(calendar[Date]), "MMM"), 3) & " " & RIGHT(YEAR(calendar[Date]), 2)You can now sort your text field by your numerical field and use this in your axis to sort correctly.
Pete
- StaceyGriffeth
Hello BA_Pete ... in this case, the business wants one entry on the X-axis for each of the 12 calendar months, with the most recent month being to the far right, and the oldest month being to the far left, with the three years of data overlayed such that each month displays the past 3 values for that month (see the picture in my post). If I have to include year to get the sort right, then it would not work for their need. Is there a way to adapt this approach so that it meets the end result I described?
- BA_Pete
I guess you could merge both my solution and Yuna's solution together:
Create both the numerical and text monthYear fields in your calendar and set the text field sort order based on the numerical field. Then place the text field into the tooltip instead of on the axis then sort on it in the chart.
This would add the monthYear text to the tooltip, but it wouldn't be as offensive/confusing as having the relative month number on there.
Pete