DAX Measure w/ Lookup value equal to Today minus # workdays (already have Dates tbl & Workday fld)

[arelf27]

I have a Dates table that has two columns: Date (all calendar dates from 2000 going forward...) and Workday that has 1 for (Mon - Fri) and 0 (if Sat or Sun).   I just need to find today's date minus 10 workdays and use it to filter out data in a visual...

I can't figure out how to do it in DAX: Select date from Dates table that's equal to Today, then minus 10 days where Workday is equal to 1. (So I'm trully counting 10 workdays from today, not just 10 days (which would include weekends)

Dates:

Date                 Workday

1/30/2018        1

1/31/2018        1 

2/1/2018          1

2/2/2018          1

2/3/2018          0

2/4/2018          0

2/5/2018          1

[Zubair_Muhammad · Accepted solution]

Hi @arelf27

You can use something like this

10 days back =
CALCULATE (
    MIN ( Dates[Date] ),
    TOPN (
        10,
        FILTER (
            ALL ( Dates ),
            Dates[Date] < SELECTEDVALUE ( Dates[Date] )
                && Dates[Workday] = 1
        ),
        [Date], DESC
    )
)

[arelf27]

The TopN function worked beatifully! Thank You 

I substituted: Dates[Date] < SELECTEDVALUE ( Dates[Date] ) with just  Dates[Date] < TODAY()

[jthomson]

If it's truly just a list of weekday = 1, weekend = 0 and doesn't take into account public holidays then you could just subtract 14 from your current date column, but I'm guessing it actually does...

[Zubair_Muhammad]

@arelf27