Next Working Day

[lherbert501]

Hi,

I have a query that gives me the 1st day of the previous month and it works grerat.

1stDayPrevMonth =

VAR __prevMonthDay = EOMONTH(MAX(RS_Dates[DateFull]),-1)+1

RETURN

CALCULATE(

        [Total Sales],  

      Sales_Snapshot[Date Added] = __prevMonthDay

)

Trouble is the 01/10/22 and 02/10/22 was a weekend. How would I add to my query to move onto the next working day?

I have working day flags set up etc already,

Thanks

Liam 

[johnt75 · Accepted solution]

You could use

1st Working Day Prev Month =
VAR __prevMonthDay =
    EOMONTH ( MAX ( RS_Dates[DateFull] ), -1 ) + 1
VAR __PrevMonthWorkingDay =
    CALCULATE (
        MIN ( RS_Dates[DateFull] ),
        REMOVEFILTERS ( RS_Dates[DateFull] ),
        RS_Dates[DateFull] >= __prevMonthDay,
        RS_Dates[Is Working Day] = TRUE ()
    )
RETURN
    CALCULATE ( [Total Sales], Sales_Snapshot[Date Added] = __PrevMonthWorkingDay )

[lherbert501]

Thankyou @johnt75, really appreciate it 🙂

Works great