Last 4 years on table

[ricardo_martins]

Hi all,

I'm trying to build what feels like a simple DAX measure — but clearly, it’s not as straightforward as it looks, because it’s driving me slightly insane.

Goal:

I need a measure that shows on a table, let's say, sales for the last 4 years, dynamically based on context.

• If no year is selected, I want 4 rows: 2025, 2024, 2023, and 2022.

• If I slice 2022, then it should shift and show: 2022, 2021, 2020, and 2019.

The [Sales] measure already exists — I just need the logic for the year context.

Where I'm stuck:

I'm not that strong with DAX, so I’ve tried everything I know… and everything I don’t, just vibe coding my way through half-baked ideas.

I’ve gone through:

• CALCULATE with ALL, ALLSELECTED, FILTER, VALUES…

• Disconnect slicer from table visual.

• Capturing min/max years before return

• Using IF and BLANK()

• Helper measures as visual filters.

• You name it...

Every attempt ends up falling short:

• I get the correct 4 rows but is does not respond to the year slicer

• I get the correct 4 rows but when slicing I only get one row

• I get a table with all years with the same value for every row

So… any help from someone who’s been through this rabbit hole (and made it out sane) would be amazing.

Thanks in advance!

[techies]

Hi @ricardo_martins are you trying to do something like this ? Mine is at the quarter level, but the logic is the same

[ricardo_martins]

hi @techies, I think so, I had missed a video share previously so I went for that solution. Nevertheless thanks for your reply and support!

[johnt75 · Accepted solution]

Not sure that I can make it clearer than SQLBI, but I'll try to explain the general principles.

You need a second copy of the date table, because you want to be able to select a single year and yet display multiple years in the visual. So you select a single year from the main date table, and you can then use dates from the copy inside the visual to show the values that you want to.

The main concept is that you take the value selected from the main date table and use that to generate the list of dates that you want to show in the copy table. The rest is about making sure that the list of dates in the copy table correctly filter the main fact table.

I'd suggest that you watch the video, then read the article and let it sink in a bit. Watch the video again, and then try and copy the steps that Alberto details in the article and put them into your model. Also, download the sample file from the article and have a look at how it works in there.

[ricardo_martins]

I had the video blocked because of cookies! Totally missed that! Thanks for pointing that out.

Please don't take me the wrong way, you've been such a great help, but for my understanding, I can work with a mock calendar table and for what I got from the video (I could replicated it easily but to be honest I still can't follow some of the logic), I can also use the same calculation group to implement some other time series like MAT or last 2 years, etc., but if I have another case like this where the slicer cannot directly affect the dimension on the visual I need a new mock table right? Won't this make a more complex model very convoluted? This will also make a self-service scenario unworkable as normal user won't known that some measures need the "real" dimension, and others need the "mock" dimension to be placed on the visual. I am seeing this correctly?

Is this really the best approach for these scenarios?

I don't mean to sound ungrateful, or too argumentative, or even if this is the right place fr this, but I really wanted to understand the logic behind Power BI.

[johnt75]

It shouldn't really add too much complexity to a model. Any model which is dealing with time-related data should already have a date table, and that is the table which should be used in all slicers and almost all visuals.

The duplicate date table, 'Previous Date', only has a relationship to the date table and that relationship can be made inactive and only activated in the appropriate calculation group. With an inactive relationship 'Previous Date' has no impact on the rest of the model.

If you wanted to you could hide both 'Previous Date' and the associated calculation group from the report view, so that self service users would never see them, and thus avoid any potential confusion.

Perhaps a use case would help you to understand the thinking behind the approach. We have many report pages where we want to show a lot of data, across multiple visuals, for a single month. But we also want to show e.g. 6 months worth of data for selected measures in a chart, so that users can see how the data is trending.

In this case, the user selects a single month from a slicer, driven by the main date table. This filters all the visuals so that we're only showing data for the chosen month. Using the technique from SQLBI we can take that single month selection and still show the trend chart going back 6 months.

You can add as many periods as you want as different calculation items, and they can all be at different granularities of date, so you could have calculation items for the last 6 months, the last 2 years and the last 90 days. All that would change in the calculation items would be the logic defining which dates from 'Previous Dates' should be used, and you could use different granularities from the 'Previous Dates' table on the visuals, e.g. using quarters, months or days.

I'm not 100% sure what you mean by "another case like this where the slicer cannot directly affect the dimension on the visual" but there are plenty of cases where helper tables are needed. These are very often not connected to any other tables in the model, and can be used for dynamic segmentation, e.g. to group sales volumes into bands. These do add additional tables to the model, but as long as they are clearly named to identify what they are they do not need to add any layers of confusion.

[ricardo_martins]

Thanks @johnt75 but I'm slightly dumb, is there an easier guide or some more complete example?

I've heard about calculation groups but I'm not really familiar with the concept.

Sorry for that and really appreciated the help!

[FBergamaschi]

Hi @ricardo_martins the example is not complex but I undersstand your reply

If you can share your pbix via a could service, I can implement that and send it back

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[ricardo_martins]

hi @FBergamaschi, thanks for your response and availability to help, I went with @johnt75 solution as I felt it would be very lazy for me to just ignore the solution where I needed to to some work instead of having someone do it for me. Thanks again anyway!

[johnt75]

You can adapt the techniques in https://www.sqlbi.com/articles/show-previous-6-months-of-data-from-single-slicer-selection/  to show 4 years instead of 6 months.