Forum Discussion

afree's avatar
afree
Frequent Visitor
3 years ago

Need Help with DAX 2-Day Lag Formula return different data sources based on future vs. past

Hi,

I am trying to create a DAX formula that will return hours depending on the day. Please help! I am very stuck.

 

For past dates before TODAY and YESTERDAY, I want to return the data from "Actual Hours" measure/column.

For TODAY, YESTERDAY and FUTURE DAYS, I want to return the data from "Scheduled Hours" measure/column.

 

I am currently using the below formula but it is not bringing in "Scheduled Hours" for today and future. The hours return for past dates before Yesterday and Today are incorrect. I am comparing this data in a table matrix by bringing in Actual Hours and Scheduled Hours side by side.

 

2 Day Lag Hours =
VAR CurrentDate = SELECTEDVALUE('Tables- Dates'[Date])
VAR Result =
    SWITCH(
        TRUE(),
        CurrentDate < TODAY() - 1,[Actual Hours], //Past: Actual Hours Measure
        CurrentDate <= TODAY(), [STAR Scheduled Hours], //Today and Yesterday: Scheduled Hours Measure
        CurrentDate > TODAY(), [STAR Scheduled Hours] //Future: Scheduled Hours Measure
    )
RETURN
Result
 
Thank you!
"DAX"
"DAY LAG"
"FORMULA"
"SWITCH"
"VAR"

2 Replies

  • some_bih's avatar
    some_bih
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Hi afree I am courious, isn't YESTERDAY already less than TODAY? So you only need reference to <TODAY and second scenario >TODAY. Hope this help

  • technolog's avatar
    technolog
    Icon for Super User rankSuper User
    2 years ago

    I hope this message finds you well. I've noticed that this solution remain unresolved. If any of you have managed to find a resolution to the issue, I kindly request that you share your solution for the benefit of the entire community. Alternatively, if you're still facing challenges, please do let us know as well.

    Your insights and updates will greatly assist others who might be encountering the same challenge.