Forum Discussion

Mark_Ball's avatar
Mark_Ball
Frequent Visitor
4 years ago
Solved

DAX Help: Calculate second last working day

Good day, Everyone I need some DAX help. Our Operations wants to measure activity in the last couple ‘Workdays’ of each month (our heaviest billing days). Workdays will be considered M-F (holidays a...
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Mark_Ball ,

     

    I think you can try this code to create a calculated column.

     

    Second last workday = 
CALCULATE (
    MAX ( DimDate[Date] ),
    FILTER (
        DimDate,
        DimDate[Workday] = 1
            && DimDate[FirstOfMonth] = EARLIER ( DimDate[FirstOfMonth] )
            && DimDate[Date] < DimDate[Last Work Day]
    )
)

     

    Result is as  below.

    We can see that in January 2022, last work day is 2022/01/31 (Monday), the second work day is 2022/01/28.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi Mark_Ball ,

 

I think you can try this code to create a calculated column.

 

Second last workday = 
CALCULATE (
    MAX ( DimDate[Date] ),
    FILTER (
        DimDate,
        DimDate[Workday] = 1
            && DimDate[FirstOfMonth] = EARLIER ( DimDate[FirstOfMonth] )
            && DimDate[Date] < DimDate[Last Work Day]
    )
)

 

Result is as  below.

We can see that in January 2022, last work day is 2022/01/31 (Monday), the second work day is 2022/01/28.

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.