Trailing 12 months, year over year

[StaceyGriffeth]

Hello,

I have a table of sales data.  I would like to create a clustered bar chart where the x-axis is the trailing 12 months (e.g. today it would display April - March), and each month would have bars for most recent month's sales, same month prior year, and same month 2 years prior.  Below is a mock-up of what I am trying to accomplish.  When the calendar rolls to a new month, the months on the chart would shift one to the left (e.g. Apr - Mar would become May - Apr).  Can anyone help a girl out?

[v-yuaj-msft · Accepted solution]

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.

[v-yuaj-msft]

Hi @StaceyGriffeth ,

@BA_Pete 's idea is great.  I did some test in your sample file. Is the following result what you want?

Best Regards,

Yuna

[StaceyGriffeth]

@v-yuaj-msft  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.

[v-yuaj-msft · Accepted solution]

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.

[mruizprieto17]

Hello,

Can you shere you pbix file? I try to resolve similar case. In my case, I create a calendar to select one slice for  "End of Month Date".  My dask fuction is:

And other similar where add the line currentdate = currentdate-365. I try to join to mesaure but don't work well. If you represent for all date is correct but not when i group by month. I try to create a relative month but power bi don't work and all relative month is similar in the interaction with to date.

link of my pbix: https://drive.google.com/file/d/1fteQUo8Zk37k-Hf55FQs7PCVe66155-7/view?usp=sharing

[StaceyGriffeth]

@v-yuaj-msft Thank you so much for your help!

[StaceyGriffeth]

@v-yuaj-msft  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]

Hi @StaceyGriffeth ,

Apologies for the late reply, Easter break 🙂

You can do what @v-yuaj-msft 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 · Accepted solution]

@StaceyGriffeth ,

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

[StaceyGriffeth]

@BA_Pete Thank you so much for your help!

[BA_Pete]

Hi @StaceyGriffeth ,

My first recommendation would be to add a [relativeMonth] field into your calendar table. This will solve part of this issue for you and many more in the future. You can create this field in Power Query like this:

relativeMonth =
(Date.Year([date]) * 12 + Date.Month([date])) - (Date.Year(DateTime.LocalNow()) * 12 + Date.Month(DateTime.LocalNow()))

Or in DAX like this:

relativeMonth =
(YEAR(calendar[Date]) * 12 + MONTH(calendar[Date])) - (YEAR(TODAY()) * 12 + MONTH(TODAY())

Once you have this, the trailing 12 months part is as easy as filtering your visual by relative months 0 to -11.

For the actual values, you want measures something like this:

actualSales = SUM(yourTable[Sales])

actualSalesTY-1 =
CALCULATE(
  [actualSales],
  SAMEPERIODLASTYEAR(calendarTable[Date])
)

actualSalesTY-2 =
CALCULATE(
  [actualSales],
  DATEADD(
    calendarTable[Date],
    -2,
    YEAR
  )
)

Pete

[StaceyGriffeth]

I think I have the second part figured out, but getting the months right is baffling me.  I tried creating the relative month, but for older years I had values like -50, -49, etc, because it's subtracting the year.  So then I tried it with just month:  

RelativeMonth = (MONTH(Calender[Date]))-(MONTH(Calender[today]))

I'm getting 0 for March, -1 for Feb, and -2 for Jan (which seems correct), but April is 1, May 2, and so on.  So I don't see how this would get the months to sort properly.  What am I missing here?

[BA_Pete]

@StaceyGriffeth ,

That's correct, it will always assign current month as 0, then last month -1, and so on ad infinitum depending how far back your calendar goes. Likewise, next month is +1, the one after that +2 etc.

You add this field as a visual-level filter and set it to 0, -1, -2 ... -11. This will then always show current month and the previous 11 months in your visual, no matter what the date/month.

Pete

[StaceyGriffeth]

I'm sorry, I'm just having a hard time wrapping my head around this one.  I don't think I've had enough coffee yet.  🙂

If I'm trying to get 3 years of sales on a 12-month bar chart, how would I get those months prior to -11?  So that for March, it has 3 bars clustered - Mar 2021, Mar 2020, Mar 2019.  Feb and Jan would be likewise.  Dec would be 2020, 2019, 2018.  It seems like the 0 to -11 approach would only give me the past 12 months, no?

[BA_Pete]

@StaceyGriffeth ,

You don't need 3 years' worth of dates on your chart per se.

When you use functions like DATEADD and SAMEPERIODLASTYEAR Power BI allocates the prior years' values to the CURRENT year's dates.

Put your dates in a table, then put the three measures I provided in as well and see which dates the prior year values are assigned to.

Pete

[StaceyGriffeth]

Ah yes, that is working!  I double-checked the monthly sales amounts against the raw data and it's correct.  But how do I get the months in order of the current month being last (far right)?  It's currently in Jan - Dec order.

[BA_Pete]

@StaceyGriffeth ,

You'll need a field in your calendar to sort on. In this scenario, I would recommend sorting on your relative month field.

Go to your calendar in the data view, select your month field, then on the ribbon select 'Sort by column'. In the dropdown list, select your new relative month field.

Back on your chart, sort on month ascending and this should work as required.

Pete

[StaceyGriffeth]

@BA_Pete I had thought of that, but I cannot sort by relative month because there is more than one relative month value per month name as the data goes over several years.

[amitchandak]

@StaceyGriffeth , With help from time intelligence. Use relative date slicer to select last 12 month

monthly sales = Sales[Sales Amount])

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

2 Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-2,Year))

or

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))

last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))

2nd last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-24,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 :radacad sqlbi My Video Series Appreciate your Kudos.

[StaceyGriffeth]

Hello @amitchandak .  I have that part worked out, but I can't figure out how to get the months on the x-axis to shift each month so that the most recent month is at the end.