Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hey there,
I have a calculation created by datesbetween. I used Today()-7 function for the start and Today() for end date. BUt now I want to change it with a dynamic date which comes from my date slicer. Can anyone help me on this please?
Solved! Go to Solution.
Add the variable in a measure to see whether it returns the correct date
e.g.
Measure MIN_DATE = CALCULATE(MIN('Calendar'[Date]),ALLSELECTED('Calendar'[Date]))
Hi,
Replace today()-7 with min(calendar[date]) and today() with max(calendar[date]). Ensure that the Date slicer is built from the Calendar Table.
Hello @Kira_Net
Considering you have a Calendar table, you need to write 2 measures or variables and call these measures in datesbetween function.
Measure =
var _min_date = CALCULATE(MIN('Calendar'[Date]),ALLSELECTED('Calendar'[Date]))
var _max_date = CALCULATE(MAX('Calendar'[Date]),ALLSELECTED('Calendar'[Date]))
RETURN
I have similar approach for my problem statement to count no. of rows between two dates
but with this solution, its showing me the count of all rows.
Hey @themistoklis Thank you. I used the variable for the start and end date but it still showing me blank on my score card. the calculation works fine when I use today() and today ()-7 for start and end date but it only gives me the last week result. I wanted to create the flexeblity for users to filter out date using slicer.
Add the variable in a measure to see whether it returns the correct date
e.g.
Measure MIN_DATE = CALCULATE(MIN('Calendar'[Date]),ALLSELECTED('Calendar'[Date]))
Create a detached calendar (list of dates).
Drad calender to a slicer with a slicer bar (with min and max range)
Create dax measure ....
VAR mindate = MIN(calandar(date))
VAR maxdate = MIN(calandar(date))
RETURN
DATEDIFF(mindate , maxdate , DAY )
I am not sure if this is exactly what you need, but I hope you can change the value as required.
Please click the thumbs up and accept as solution button. Thank you ! 😎
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
88 | |
85 | |
82 | |
65 | |
49 |
User | Count |
---|---|
138 | |
110 | |
104 | |
66 | |
64 |