topic Re: Need Help with DAX 2-Day Lag Formula return different data sources based on future vs. past in DAX Commands and Tips

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

afree — Thu, 31 Aug 2023 13:36:59 GMT

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!

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

some_bih — Thu, 31 Aug 2023 20:10:28 GMT

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

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

technolog — Sun, 24 Sep 2023 08:09:15 GMT

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.