Forum Discussion
Show dynamic months on the visuals when user is using a single slicer selection
hello,
I have a slicer with dates and I want that when selecting, for example, the year/month 2025/02, all the visuals I have always display the selected month and the end of the last 3 years, that is, in this example I would have 2025/02, 2024/12, 2023/12 and 2022/12.
My date table is always the last day of the month.
Can anyone help me? I only found examples that give n consecutive months
Follow the steps in https://www.sqlbi.com/articles/show-previous-6-months-of-data-from-single-slicer-selection/ to set up the Previous Dates table and the relationship. Instead of the calculation item in the article, create a calculation item like
End of last 3 years = VAR CurrentDate = MAX ( 'Date'[Date] ) VAR CurrentYear = YEAR ( CurrentDate ) VAR CurrentMonth = DATESMTD ( 'Date'[Date] ) VAR Dec1 = CALCULATETABLE ( DATESMTD ( 'Date'[Date] ), TREATAS ( { DATE ( CurrentYear - 1, 12, 31 ) }, 'Date'[Date] ) ) VAR Dec2 = CALCULATETABLE ( DATESMTD ( 'Date'[Date] ), TREATAS ( { DATE ( CurrentYear - 2, 12, 31 ) }, 'Date'[Date] ) ) VAR Dec3 = CALCULATETABLE ( DATESMTD ( 'Date'[Date] ), TREATAS ( { DATE ( CurrentYear - 3, 12, 31 ) }, 'Date'[Date] ) ) VAR PreviousDates = TREATAS ( UNION ( CurrentMonth, Dec1, Dec2, Dec3 ), 'Previous Dates'[Date] ) VAR Result = CALCULATE ( SELECTEDMEASURE (), REMOVEFILTERS ( 'Date' ), KEEPFILTERS ( PreviousDates ), USERELATIONSHIP ( 'Previous Date'[Date], 'Date'[Date] ) ) RETURN ResultApply that calculation item as a filter on the visuals you want.
2 Replies
johnt75Super User
Follow the steps in https://www.sqlbi.com/articles/show-previous-6-months-of-data-from-single-slicer-selection/ to set up the Previous Dates table and the relationship. Instead of the calculation item in the article, create a calculation item like
End of last 3 years = VAR CurrentDate = MAX ( 'Date'[Date] ) VAR CurrentYear = YEAR ( CurrentDate ) VAR CurrentMonth = DATESMTD ( 'Date'[Date] ) VAR Dec1 = CALCULATETABLE ( DATESMTD ( 'Date'[Date] ), TREATAS ( { DATE ( CurrentYear - 1, 12, 31 ) }, 'Date'[Date] ) ) VAR Dec2 = CALCULATETABLE ( DATESMTD ( 'Date'[Date] ), TREATAS ( { DATE ( CurrentYear - 2, 12, 31 ) }, 'Date'[Date] ) ) VAR Dec3 = CALCULATETABLE ( DATESMTD ( 'Date'[Date] ), TREATAS ( { DATE ( CurrentYear - 3, 12, 31 ) }, 'Date'[Date] ) ) VAR PreviousDates = TREATAS ( UNION ( CurrentMonth, Dec1, Dec2, Dec3 ), 'Previous Dates'[Date] ) VAR Result = CALCULATE ( SELECTEDMEASURE (), REMOVEFILTERS ( 'Date' ), KEEPFILTERS ( PreviousDates ), USERELATIONSHIP ( 'Previous Date'[Date], 'Date'[Date] ) ) RETURN ResultApply that calculation item as a filter on the visuals you want.
Thank you very much, it's perfect
