Forum Discussion
How to get date that is 7th working days from any specific date
Can someone help me to get the 7th working day (-7) from today in DAX
for eg: if today is 5/13/2020 then it should return 5/4/2020 excluding (5/9 and 5/10 weekend)
PragatiM Net Work Days returns the number of working days between two dates, true. But it is the technique that I was pointing you to. In your case, you would create a table variable using CALENDAR that would be from your date to your date -9 (you can just subtract 9 from your date to get your starting date for CALENDAR). You would use ADDCOLUMNS to add a WEEKDAY column using 2 as the second parameter. You then FILTER this to remove rows where your weekday column is greater than 5. Now you use ADDCOLUMNS again to count how many days each date is away from your target date. You then FILTER this to 7 and that's your date.
If you want a specific implementation, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
4 Replies
- Greg_Deckler
Community Champion
PragatiM - You should be able to use the technique here for Net Working Days - https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362#M109
The basic technique is that you use WORKDAY with the 2 parameter and exclude workdays that are > 5
Hi Greg,
Thank you for your reply. For my scenario, the NetWorkingDays is fixed number, ie. NetWorkingDays = 7. I would need a Dax function that would return the date instead by subtracting 7 working days from current date or the selected date. I need this to find the average of 7 working days. Please let me know if you have solution for this.
Thanks,
Pragati
- Greg_Deckler
PragatiM Net Work Days returns the number of working days between two dates, true. But it is the technique that I was pointing you to. In your case, you would create a table variable using CALENDAR that would be from your date to your date -9 (you can just subtract 9 from your date to get your starting date for CALENDAR). You would use ADDCOLUMNS to add a WEEKDAY column using 2 as the second parameter. You then FILTER this to remove rows where your weekday column is greater than 5. Now you use ADDCOLUMNS again to count how many days each date is away from your target date. You then FILTER this to 7 and that's your date.
If you want a specific implementation, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
