Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Previous Workday

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))
 
 
  • OwenAuger's avatar
    OwenAuger
    5 years ago

    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

2 Replies

  • OwenAuger's avatar
    OwenAuger
    Super User
    5 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Perfect, thanks!