Forum Discussion
Show previous 6 months dynamically based on slicer selection
-- Generally, you should not perform operations on dates -- in your fact table. First of all. You should -- always use the DimDate dimension to perform -- any kind of time-based calculations. -- Let's assume that DimDate[Date] is connected with 1:many -- to StaffTable[Pay Period Ending] and DimDate is -- marked as a Date table in the model. -- This should be the first base measure: [Total Headcount] = SUM ( StaffTable[Headcount] ) -- Then.. [Total Headcount (6M)] = VAR __lastVisibleDate = MAX ( DimDate[Date] ) VAR __endOfLastVisibleMonth = ENDOFMONTH ( 'DimDate'[Date] ) VAR __firstDate = EDATE ( __endOfLastVisibleMonth, -6 ) var __timeIntervalOfInterest = DATESBETWEEN( DimDate[Date], __firstDate, __endOfLastVisibleMonth ) var __value = CALCULATE( [Total Headcount], KEEPFILTERS( __timeIntervalOfInterest ) ) return __value
Best
Darek
Hi Anonymous ,
My DimDate table is marked as a Date table and it's connected to the StaffTable using a 1:M relationship. However, when i select a date, it's still not showing me the previous 6 months on the bar chart.
For example, if i 31st March 2019, I want it to show the headcount in a bar graph 6M prior to that so from Oct 2018 ot March 2019. But when i select it at the moment, it only shows the pay period selected.
Here's the PBIX file for reference.
Thank you!That's because when you select something from your slicer, you also filter the dimension from which the slicer's values come. This is why you only see the dates that fall into the interval you've selected in your slicer. Just change the slicer to the variety called "Before Slicer". You can find it in the properties of the slicer. Looks like this:
The "Before" Slicer
The slicer you see always filters the dates from "the beginning of time" to the selected day. But your measure only shows values for the last 6 months. This is exactly what happens in the picture above, can you see?
Best
Darek