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

[afree]

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!

[technolog]

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.

[some_bih]

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