topic Re: Previous Workday in DAX Commands and Tips

Previous Workday

Anonymous — Fri, 26 Mar 2021 14:54:34 GMT

So I have a calendar table and was able to create a calculated column that tags each workday with a 1. How would I create a calculated column to tag a day as the previous workday?

Here is the dax for my workday column...

WorkDay = IF(OR('Calendar'[WeekDay]=1,'Calendar'[WeekDay]=7),0,IF(ISBLANK('Calendar'[Holiday]),1,0))

Re: Previous Workday

OwenAuger — Sat, 27 Mar 2021 00:19:57 GMT

Hi Anonymous

Just confirming, are you wanting a 0/1 flag that is equal to 1 for the workday immediately before the current date when the table is refreshed/processed (i.e. TODAY() )?

If so, something like this should work:

Previous WorkDay = VAR ReferenceDate = TODAY () VAR MaxWorkDayBeforeReferenceDate = CALCULATE ( MAX ( 'Calendar'[Date] ), ALL ( 'Calendar' ), 'Calendar'[Date] < ReferenceDate, 'Calendar'[WorkDay] = 1 ) RETURN INT ( 'Calendar'[Date] = MaxWorkDayBeforeReferenceDate )

If you instead want a column that returns the date of the workday preceding the current row's date, something like this should work:

Previous WorkDay Date = VAR ReferenceDate = 'Calendar'[Date] RETURN CALCULATE ( MAX ( 'Calendar'[Date] ), ALL ( 'Calendar' ), 'Calendar'[Date] < ReferenceDate, 'Calendar'[WorkDay] = 1 )

Regards,

Owen

Re: Previous Workday

Anonymous — Mon, 29 Mar 2021 12:45:32 GMT

Perfect, thanks!