Using Selected Date in Measure

[Anonymous]

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!

[v-yadongf-msft · Accepted solution]

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.

[djurecic]

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