Forum Discussion
Filter 3 months based on slicer selection
Hello all,
I have a matrix based on a datasource and I need to show 3 months based on a slicer selection. I have 2 slicers, one showing years and one showing month names. The requriement is that when the user selects the year and month the matrix shows data for the selected month and the 2 months after that. The matrix shows full months, so from the first to the last of the 3rd month.
So if a user select 2023 and February then the matrtix needs to show all data from 1st Feb 2023 to 30 April 2023.
I tried implementing some of the suggested solutions from here but i just can't make it work, not sure how to limit any of these to my requirements:
https://radacad.com/power-bi-from-and-to-date-filtering-with-one-slicer
Any help is greatly appreciated!
This did help in a way. There was a comment pointing to another video which helped me do this. Essentially my approach was to use Tabular Editor to create the filter which is applied to any measure, which was my preference.
The video covers that in the second half as the first half is about explaining the formula and which fields to use in the visual and slicers:
https://www.youtube.com/watch?v=d8Rm7dwM6gc&ab_channel=SQLBI
You need to create a Date table and then duplicate that. My final formula is this:
VAR NumofMonths = 3 VAR ReferenceDate = MIN('Date Table 1'[Date]) VAR SecondaryDate = DATESINPERIOD( 'Date Table 2'[Date], ReferenceDate, NumofMonths, MONTH ) VAR Result = CALCULATE( SELECTEDMEASURE(), REMOVEFILTERS('Date Table 1'), KEEPFILTERS(SecondaryDate), USERELATIONSHIP('Date Table 1'[Date],'Date Table 2'[Date]) ) RETURN ResultAs i was looking at a slicer to show me dates in the future i used MIN for my ReferenceDate. If looking for dates in the past then use MAX I think.
3 Replies
PadycosmosSolution Sage
This did help in a way. There was a comment pointing to another video which helped me do this. Essentially my approach was to use Tabular Editor to create the filter which is applied to any measure, which was my preference.
The video covers that in the second half as the first half is about explaining the formula and which fields to use in the visual and slicers:
https://www.youtube.com/watch?v=d8Rm7dwM6gc&ab_channel=SQLBI
You need to create a Date table and then duplicate that. My final formula is this:
VAR NumofMonths = 3 VAR ReferenceDate = MIN('Date Table 1'[Date]) VAR SecondaryDate = DATESINPERIOD( 'Date Table 2'[Date], ReferenceDate, NumofMonths, MONTH ) VAR Result = CALCULATE( SELECTEDMEASURE(), REMOVEFILTERS('Date Table 1'), KEEPFILTERS(SecondaryDate), USERELATIONSHIP('Date Table 1'[Date],'Date Table 2'[Date]) ) RETURN ResultAs i was looking at a slicer to show me dates in the future i used MIN for my ReferenceDate. If looking for dates in the past then use MAX I think.