Forum Discussion
Using Selected Date in Measure
Hi All,
I am looking to use a slicer to select a date in the future to view how many projects are categorized as "Done", "In Progress", or "Scheduled". My Current DAX looks like:
Status =
VAR CurrentDate = Today()
RETURN
SWITCH(
TRUE(),
AND(CurrentDate >= MAX(timeline[Start Date]), CurrentDate <= MAX(timeline[End Date])), "In Progress",
AND(CurrentDate >= MAX(timeline[Start Date]), CurrentDate >= MAX(timeline[End Date])), "Done",
"Scheduled")
I am looking to use the value from a Date Slicer but SELECTEDVALUE(DATE[DATE]) does not seem to work.
However using a random date in the future like below gets my expected output:
Status =
VAR CurrentDate = DATEVALUE("10/01/2025")
RETURN
SWITCH(
TRUE(),
AND(CurrentDate >= MAX(timeline[Start Date]), CurrentDate <= MAX(timeline[End Date])), "In Progress",
AND(CurrentDate >= MAX(timeline[Start Date]), CurrentDate >= MAX(timeline[End Date])), "Done",
"Scheduled")
I am looking to get a user input for VAR CurrentDate.
Thank you!
Hi Anonymous ,
This is my test table:
Date table:
Create a measure:
Status = VAR MINDate = MIN('Table'[Date]) VAR MAXDate = MAX('Table'[Date]) RETURN SWITCH ( TRUE (), MINDate <= MAX(timeline[Start Date]) && MAXDate >= MAX(timeline[End Date]), "In Progress", MINDate >= MAX(timeline[Start Date]) && MAXDate >= MAX(timeline[End Date]), "Done", "Scheduled" )Create a slicer form date table and create a table visual from timeline table:
You can select a date in the future to view how many projects are categorized as "Done", "In Progress", or "Scheduled":
Best regards,
Yadong Fang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi Anonymous ,
The slicer may be filtering out any dates other than the selected date, so you won't get any results where the date is < or >.
You can use the All function on the timeline table to escape the slicer filtering.https://learn.microsoft.com/en-us/dax/all-function-dax
Hi Anonymous ,
This is my test table:
Date table:
Create a measure:
Status = VAR MINDate = MIN('Table'[Date]) VAR MAXDate = MAX('Table'[Date]) RETURN SWITCH ( TRUE (), MINDate <= MAX(timeline[Start Date]) && MAXDate >= MAX(timeline[End Date]), "In Progress", MINDate >= MAX(timeline[Start Date]) && MAXDate >= MAX(timeline[End Date]), "Done", "Scheduled" )Create a slicer form date table and create a table visual from timeline table:
You can select a date in the future to view how many projects are categorized as "Done", "In Progress", or "Scheduled":
Best regards,
Yadong Fang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
