Forum Discussion
Capture Slicer Settings -Relative Date
Hi,
I have a Date Slicer which is connected to my Date field in the Date table. Under slicer settings, I have selected Relative Date. Is it possible to capture the value a users selects in this into a measure? For example if a user selects Last 10 Days, or Last 2 Months, I want that information captured and converted to days so that I can use this value to calculate average based on the number of days elsewhere.
Is this possible?
Thanks.
Hi, armchairexpert
Thanks for ajohnso2's reply. You can try following measure to get the min and max date, then calculate the difference between the date. It's worth noting that if you create two slicers, the two slicers will affect each other, so you'll need to create two Date fields for it to workDay Diff 1 = VAR _minDate = MIN('Date'[Date]) VAR _maxDate = MAX('Date'[Date]) VAR _dayDiff = INT(_maxDate - _minDate) RETURN _dayDiff Day Diff 2 = VAR _minDate = MIN('Date 2'[Date]) VAR _maxDate = MAX('Date 2'[Date]) VAR _dayDiff = INT(_maxDate - _minDate) RETURN _dayDiffBest Regards,
Yang
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
4 Replies
Hi, armchairexpert
Thanks for ajohnso2's reply. You can try following measure to get the min and max date, then calculate the difference between the date. It's worth noting that if you create two slicers, the two slicers will affect each other, so you'll need to create two Date fields for it to workDay Diff 1 = VAR _minDate = MIN('Date'[Date]) VAR _maxDate = MAX('Date'[Date]) VAR _dayDiff = INT(_maxDate - _minDate) RETURN _dayDiff Day Diff 2 = VAR _minDate = MIN('Date 2'[Date]) VAR _maxDate = MAX('Date 2'[Date]) VAR _dayDiff = INT(_maxDate - _minDate) RETURN _dayDiffBest Regards,
Yang
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
Hi Anonymous ,
Thanks for your response. That is exactly what I was looking for. Thanks for your help on this.
Kind regards.
You have not provided much info here so im just guessing.
If your slicer has selections such as
- Yesterday
- Last Week
- Last 10 Days etc...
You just need to build your new measure using the 'SELECTEDVALUE('Table'[Column])' syntax.
To expand.
Test Measure =
VAR _Selected = SELECTEDVALUE('Table'[Column])
RETURN
SWITCH(
TRUE(),
_Selected = 'Last 10 Days', <Do something>,
_Selected = 'Yesterday', <Do something>,
<Do something else>
)
Hi,
Thanks for your response.
As I said, it is a Relative Date. I think I will need three variables and want to capture 'Last', 'Next' or 'This' from the first drop down on the slicer, then the value 10, and anything which is selected from the next drop-down such as Days, Weeks, Months, Years etc. Then using the three values, convert the value into number of days.
Thanks
