topic Re: DAX Help: Calculate second last working day in DAX Commands and Tips

DAX Help: Calculate second last working day

Mark_Ball — Mon, 19 Sep 2022 22:54:03 GMT

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!

Re: DAX Help: Calculate second last working day

amitchandak — Tue, 20 Sep 2022 02:14:47 GMT

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

Re: DAX Help: Calculate second last working day

Anonymous — Tue, 20 Sep 2022 06:40:29 GMT

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.

Re: DAX Help: Calculate second last working day

Mark_Ball — Tue, 20 Sep 2022 18:17:38 GMT

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!