Forum Discussion
Last 4 years on table
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.
Super UserNot 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.
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.
