DAX Help: Calculate second last working day

[Mark_Ball]

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 are not a factor). In my dimDate table I have successfully calculated the last working day of each month. Now I need a calculated column to identify the second last work day of the month. If I use a formula like: ‘Last working day’ minus 1 it works for Tuesday through Friday, but Monday turns into Sunday (I would need previous Friday).

Calculated Columns in table: (Weekdays = 1, else 0)

Last Work Day =

CALCULATE(MAX([Date]),FILTER(DimDate,DimDate[Workday]=1 && DimDate[FirstOfMonth]=EARLIER([FirstOfMonth])))

What did not work for second last workday of month:

First Test =

VAR vLWD = CALCULATE(MAX([Date]),FILTER(DimDate,DimDate[Workday]=1 && DimDate[FirstOfMonth]=EARLIER([FirstOfMonth])))

VAR vResult =

    vLWD - 1

RETURN

vResult

also tried the below to provide an identifier like second last work day = 0, this worked for some months, not all:

Work Days from EoM =

VAR vDaycount = if (DimDate[Date] > [Last Work Day], -9, [Last Work Day] - DimDate[Date])

VAR vNonWorkDayCount = CALCULATE(COUNTROWS(DimDate),FILTER(DimDate,DimDate[Date] < EARLIER([EoM]) && DimDate[Date] > EARLIER([Date]) && DimDate[Workday]=0))

VAR vResult = vDaycount - vNonWorkDayCount

RETURN

vResult

Any help is appreciated!

[Mark_Ball]

Thank you both for your suggested formulas. @v-rzhou-msft 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!

[v-rzhou-msft · Accepted solution]

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.

[amitchandak]

@Mark_Ball ,

Create a new columns ---

work date = if(weekday([Date],2) <6, [Date], blank())

month end  = eomonth([Date],0)

Work day rank = if(isblank([work date]) , blank(), rankx(filter(Date,[month end]  = earlier([month end])) , [work date],,desc,dense)

Final rank = [Work day rank] - minx(filter(Date,[month end]  = earlier([month end])), [Work day rank]) +1

Because of blank rank may start with 2, so adjusted that.

Now you need final rank =2