Hi everyone! I have a page that has a slicer for the user to select a year. On that page, there are multiple charts that filter based on the year selection. The problem I’m trying to solve is that I need to have one chart that shows a count of events by month for the selected year EXCEPT when the selected year is the current year, I need it to show a monthly count for the previous 12 months. For example, as of today, we are in Feb 2023, so the user were to select 2023 as the year in the slicer, I need the chart to show Feb 2022 through Jan 2023.
In other words, if the year selected is any year except the current year, I need something like this:
If the year selected is the current year, I need something like this (assuming the current month is Feb 2023):
I know how to do each of these separately on different charts, but I am trying to have it be a single chart that shows the desired output based on the year selected.
I have uploaded a test PBIX file with some fictitious data where I built a measure and created a chart to show the last 12 months when the current year is selected, but I can’t figure out how to get it to also show the monthly breakdown for the full year if the selected year is a previous year. The measure I am using to show the last 12 month is shown below.
Total Items Past 12 Months =
VAR SelectedMonthOffset = SELECTEDVALUE(Dates[MonthOffset])
RETURN
IF(
SelectedMonthOffset <= -1 && SelectedMonthOffset >= -12,
CALCULATE(
[Total Items],
REMOVEFILTERS(Dates[Year])
),
BLANK()
)
If anyone comes up with a solution, I would love to see it!
In the meantime, what I have resorted to is having two separate charts overlaid on top of each other. The bottom chart always shows the past 12 months of data based on today’s date regardless of the year selected in the slicer. The chart that sits on top of it shows the full year’s data for the selected year on an opaque background IF the selected year is NOT the current year. If the selected year IS the current year, the chart that sits on top shows no data (because of the way my measure is written), and the background color and the title and axes fonts become transparent, so you see the chart below without realizing that there is another chart on top. The only limitation is that in that scenario, I can’t click on the chart to cross-filter the other charts on the page. Luckily, I don’t need that for this report.
One other random limitation I ran into that I hope Microsoft corrects in the future is that all font color settings for a chart’s title, axes, etc. seem to accept conditional formatting EXCEPT for the secondary Y-axis. There is no conditional formatting available for its title or values.
@ankit, thank you for the suggestion. I see that you created a disconnected table, but the behavior of the visuals is identical to the original file. I assume that is because I need to update my measure to detect whether the disconnected table is currently filtered and what the selected value is. I thought of that approach when I first began attacking this problem. However, in my actual report (not the stripped-down sample I uploaded to the forum), the year slicer filters many visuals on a single page, and many of the measures used in those visuals are already very complex without having to add extra codes to each on to test for the selected value of the disconnected table.
