Relative dates: last X calendar months, including this month

[kevhav]

Can we use the relative date slicer to do the following? Seems like a no-brainer, but I can't figure it out.

I want to show "the last 13 calendar months, including this month." So that I can show the prior 12 full months of history, plus where we're at so far this month.

Or, "the last 3 calendar years, including this year." So that I can show two full years of history, plus the current year (year-to-date).

Notice that if your slicer is for "last X days," then you get a checkbox for "Include today."

I think the natural extension of this would be: if your slicer is for "last X calendar months," then there should be a check box for "Include this month"...or, if your slicer is for "last X calendar years," then there should be a check box for "Include this year."

Is there some other way to do this that I'm missing? (Without writing the "time intelligence" into my measures!)

[JezzatheGee]

6+ years later and still no "include this month" option 😕

[darrenfishell]

If you want to do this with a live connection, it is also possible using a measure and a visual-level filter, depending on the structure of the visual, and assuming you have a proper date table in your model.

For instance, if you wanted a line chart to show only the last two years + the current period (assuming your facts are only historical records and not future budgets, etc.), you could write a measure like: 

MAXX( 'DATE',
           IF( 'DATE'[YEAR] >= YEAR( TODAY() )-2
          , 1, 0 )
)
And then add this field to visual filters and set the condition to "show items when the value is 1."

[kevhav]

I thought if only I could have one relative date slicer/filter that says "last 12 calendar months"...and another that says "this month"...and combine them using "OR" logic.

But of course multiple slicers/filters are always combined using "AND" logic
: )

So close! I feel like there has to be a way to do this!

[v-yulgu-msft]

Hi @kevhav,

Currently, the relative date slicer doesn't include current month/year when choosing last X calendar Month/Year. There is no corresponding option to change this behaviour. You may need to write the "time intelligence" into measures, either using one single slicer or using two slicers combined with "OR" logic.

Regards,

Yuliana Gu

[MaxW]

Hit this one today, It feels like a bug rather than enhancement.

I just want to do a monthly year on year line for the last 4 years, and I'm 99% there except I have to choose between ignoring this year, or truncating the oldest year. Frustrating to have to create separate calculated columns/filters as a workaround.

For anyone finding this looking for a solution, here is the True/False filter I used:

Last 4 Calendar Years = IF(Dim_Calendar[Year]>=LOOKUPVALUE(Dim_Calendar[Year],Dim_Calendar[Date],TODAY())-4,TRUE,FALSE)

[robstewart89]

You can create a calculated column in your date table :

= DATEDIFF(TODAY(), DateTable[Date], MONTH)

which will give you a value for each month in your DateTable. So this month = 0, last month = -1 and so on.

Then add this as a visual/page/report level filter where Month is less than or equal to 0, AND greater than or equal to -5. This would give you the last full 5 months plus this month so far.

Like already mentioned, the issue with using the relative date slicer is that you either have to select Last X Months which won't give you the full month of your first month or Last X Months (Calendar) which doesn't give you any of the current month. The above should hopefully help get around that. 

#example above is for last 6 months but you can replace MONTH with YEAR or whatever and change the values.

[DenisDee]

Amazing!!! Thank you

[JezzatheGee]

Brilliant, thank you from 2024

Come on Microsoft!

[DemingPDCA]

Took about 5 seconds - THANK YOU ❤️

[Anonymous]

Not all heroes wear capes! Legend!

[doost]

This is a good solution, but terrible from Microsoft that this is not a default option to choose...

[FilipLauridsen]

Great and straight forward solution, thx a lot!

[MichalMachowski]

You saved the day! Thank you!

[Anonymous]

Thank you very much !

[Anonymous]

Thank you so much. I've been looking for this everywhere.