topic Re: How to get date that is 7th working days from any specific date in Developer

How to get date that is 7th working days from any specific date

PragatiM — Wed, 27 May 2020 21:34:37 GMT

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)

Re: How to get date that is 7th working days from any specific date

Greg_Deckler — Wed, 27 May 2020 23:33:05 GMT

@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

Re: How to get date that is 7th working days from any specific date

PragatiM — Thu, 28 May 2020 13:55:15 GMT

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

Re: How to get date that is 7th working days from any specific date

Greg_Deckler — Thu, 28 May 2020 15:41:48 GMT

@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.

Re: How to get date that is 7th working days from any specific date

PragatiM — Tue, 02 Jun 2020 13:09:27 GMT

Thanks Greg,

Thank you for your suggession. I created a function that would list the7th working day for each day as per my need and then import that tabe to my tabular model for further process.