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 da...
  • 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

OwenAuger's avatar
OwenAuger
Icon for Super User rankSuper 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