Forum Discussion

lherbert501's avatar
lherbert501
Post Prodigy
3 years ago
Solved

Next Working Day

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 
Need Help
Show and Tell
  • johnt75's avatar
    johnt75
    3 years ago

    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 )

2 Replies

  • johnt75's avatar
    johnt75
    Super User
    3 years ago

    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 )