Hello all,
I've created a table that is based on a Microsoft Exchange calendar. I'm having trouble getting a day offset based on a column on column, but only if my user scheduled column has a value. It is offsetting based on the exchange calendar date. My DAX logic is a little weird, but the problem is that it works well on the desktop. However, after I publish it, the date is pushed back an extra day or two. I'm stumped as to why this is. What are your thoughts on this?
DNE Date =
var PrevDay = 'Schedule Calendar'[Start].[Date] - 1 -- **DNE date is always one day before the start date that somone works it.**
Var NewDay1 = IF( FORMAT(PrevDay,"DDD") = "Sat", PrevDay-1,
IF( FORMAT( PrevDay,"DDD") = "Sun", PrevDay-2,
PrevDay)
) -- **for testing if PrevDay is a weekend.**
Var Holi = IF( CALCULATE( MAX('Schedule Calendar'[User Scheduled]), FILTER('Schedule Calendar', NewDay1 = 'Schedule Calendar'[Start])) = "", NewDay1 -1, NewDay1) -- **for is there what no one seduled, mostly holidays.**
Var NewDay2 = IF( FORMAT(Holi,"DDD") = "Sat", Holi-1,
IF( FORMAT( Holi,"DDD") = "Sun", Holi-2,
Holi)
) -- **for if day after Holi is weekend.**
return IF( 'Schedule Calendar'[User Scheduled] = "", BLANK(),
IF('Schedule Calendar'[Start] = DATE(2022,2,17), DATE(2022,2,16), -- **2nd if added for earliest date in start, 'Holi' logic does not work for the first date and returns with extra day. this is a one off.**
NewDay2))
