Forum Discussion
rk1904
1 year agoNew Member
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 ...
- 1 year ago
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.
johnt75
Super User
1 year agoFollow 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
Result
Apply that calculation item as a filter on the visuals you want.
rk1904
1 year agoNew Member
Thank you very much, it's perfect