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!)
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:
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!
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.
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)
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.
Take a look at the September 2023 Power BI update to learn more.
Join Microsoft Reactor and learn from developers.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!
Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.