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.

Mark_Ball's avatar
Mark_Ball
Frequent Visitor
4 years ago

Thank you both for your suggested formulas. Anonymous your formula worked perfect! I also attemped yours amitchandak but the final rank formula was missing something after 'earlier([monthend]) and i couldn't finish it off to make it work for me. I appreciate everyones help!