Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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 ) )
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 ) )
The TopN function worked beatifully! Thank You
I substituted: Dates[Date] < SELECTEDVALUE ( Dates[Date] ) with just Dates[Date] < TODAY()
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...
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |