DAX - How to know which date was 5 working days before a specific date?

[elshadlive]

I need the formula to figure out the date of 5 working days (not calendar days) before a specific date. Let's assume my entry date is 19.05.2022
If I subtract 5 calendar days from 19.05.2022 then I get 14.05.2022, but it should be 12.05.2022 because there are also non-working days in that range.

In the Sales table, all dates are working dates. My first thought was to take distinct dates in that range and from the result list of dates count back 5 rows (dates). But I couldn't figure out how to do that.
I also created a Calendar table that has a one-to-many relationship with the Sales table, if that will help
Any ideas?

[v-luwang-msft]

Hi @elshadlive ,

Has your problem been solved, if so, please consider Accept a correct reply as the solution or share your own solution to help others find it.

Best Regards
Lucien

[DataInsights]

@elshadlive,

Try this measure. The concept is to count five days back, and subtract the number of weekend days in that range.

5 Working Days Before =
VAR vCurDate =
    MAX ( DimDate[Date] )
VAR vPastDate = vCurDate - 5
VAR vWeekendCount =
    CALCULATE (
        COUNTROWS ( DimDate ),
        DimDate[Date] >= vPastDate,
        DimDate[Date] <= vCurDate,
        DimDate[Weekday Flag] = 0
    )
VAR vResult = vPastDate - vWeekendCount
RETURN
    vResult

[elshadlive]

@DataInsights 
Thank you for your reply. What if there were no transactions (working days) for the last two weeks or months? It is not only about weekends. Maybe the company did not sell anything for a month. In that case, we will not have any record on the Sales table if count 5 calendar days back.
I need the 5th previous transaction day.

For example: In the following transaction dates column we have transaction records with the following dates...
10.03.2022
10.03.2022
15.03.2022
16.03.2022
16.03.2022
07.04.2022
07.04.2022
07.04.2022
28.04.2022
19.05.2022
If we start counting back 5 DISTINCT transaction days starting from 19.05.2022, then we have to get 10.03.2022.

I did not test your solution yet, but looking at this part I had a thought that it will not work if we'll not have any transactions for the previous 5 calendar days.

VAR vPastDate = vCurDate - 5

[DataInsights · Accepted solution]

@elshadlive,

Try this measure:

5 Working Days Before = 
VAR vCurDate =
    MAX ( Sales[WorkingDays] )
VAR vBaseTable =
    CALCULATETABLE (
        VALUES ( Sales[WorkingDays] ),
        ALLSELECTED (),
        DimDate[Weekday Flag] = 1,
        Sales[WorkingDays] < vCurDate
    )
VAR vRankTable =
    ADDCOLUMNS (
        vBaseTable,
        "@Rank", RANKX ( vBaseTable, Sales[WorkingDays],, DESC )
    )
VAR vResult =
    CALCULATE ( VALUES ( Sales[WorkingDays] ), FILTER ( vRankTable, [@Rank] = 5 ) )
RETURN
    vResult

Sample data:

Result: