Previous Workday

[Anonymous]

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))

[Anonymous]

Perfect, thanks!

[OwenAuger · Accepted solution]

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