Dax Formula Help - Return Date a variable # of business days back

[Kenaurec]

Hello,

I'm trying to dynamically return a single date value a variable number of business days in the past, based off user selection, that excludes weekends and holidays. For example,  if a user selects the date as September  9, 2021 in a filter and also selected options to go back 6 business days, I would want to return August 30, 2021 as the result of the measure (Sept 4-6 would be exclueded as weekend & holidays).

I'm having trouble setting up the correct formula to filter the date table and return the date.

I have a date table set up to flag weekends and holidays. I have a measure that simply sums the number of business days to go in the past, based off a user  multi-selection/filter. How can I shift the date backwards in the past, skipping weekends? 

I have tried working with DateAdd and DatesInPeriod but i'm going in circles now and running into formula structure issues with scalar valuesand mulitple column errors. Below is part of what I think I need for the solution but I'm unsure where to go next.

Variable Date Measure = 
 
Var SelecteDate = SelectedValue(DateDim[Date] // user selected date

Var Daysback =  [ Sum of Days Back]    // total value of user selected # of  business/working days to go back.

Var DateTableFilterOne =  Filter(

All(Dimdate),

Dimdate[date] < SelectedDate

&& Dimdate[Workday] =True()

) // workday accounts for  holiday flag.

// More variables or help needed to refine the filter varibale and shift it back the selected amount.

 Return

//Something to return the date 😀

Looking for some ideas or direction to resources that could help.

[daxer-almighty · Accepted solution]

@Kenaurec 

Creating 21 columns? Never. That would be totally rigid and not scalable. All you have to do is to first have a proper calendar table in PQ. Then take a crossjoin (creating a separate table) of all the dates with all the values that you should have in another table storing all the values of the "DaysBack" attribute. This will be the first two columns in my table above (Date, DaysBack). Then all you have to do is to calculate the third column (ResultingDate) which should be easy because you do have a proper calendar in there already. Let's say that your business days in the calendar are really numbered like you say (disregarding any hols and weekends). Then, for each row in the crossjoin find the date that's removed DaysBack from the current date in the row using the calendar. How to do it? Well, join the crossjoin to the calendar on Date and you'll be able to find the Date's number. Since you also have the DaysBack value on the row, you'll be able to calculate the ResultingDate's number. And once you've got the number, you can again join to the calendar but this time on the said number and hence retrieve the ResultingDate.

[Kenaurec]

 I appreciate the help, patience and numerous responses. Cheers!

[daxer-almighty]

@Kenaurec 

Have you finally been able to get it the way I described?

[Kenaurec]

😀Yes - I followed your directions  and was able to build a table that matches and is scalable. I would not have approached this in M without your input, so thanks. 

[daxer-almighty]

That's rather easy. You can create a table (best in Power Query) that will map any particular date from your date table into a tuple: (number of business days back, the date you obtain by doing the calculation). Since I believe the number of days back is a dimension on its own and has a limited number of options, this should be easy and efficient. Once you have such a hidden mapping table, it's dead easy to retrieve the date you want.

[Kenaurec]

Thank you for the suggestion, but I'm struggling to understand this. Are you able to provide a short/further example of what you mean? 

How would I go about creating this other date table?  The maximum date adjustment the user can select would be 21 business days, so am I making a  table that contains  all 21 possible dates for each date?  How would I filter out the resulting dates that are on a holiday or weekend?

 I feel like I'm misinterpreting something.

[daxer-almighty]

The beauty of the solution lies in the fact that you don't have to filter anything out when the table has been created because you make the table in a way that already filters out dates of no interest.

Here's your hidden auxiliary table:

Date	DaysBack	ResultingDate
A	1	calculated date that's 1 day back from A disregarding hols and weekends
A	2	calculated date that's 2 days back from A disregarding hols and weekends
A	3	calculated date that's 3 days back from A disregarding hols and weekends
and so on for each date and each DaysBack number

What's not to understand here? Once you've got the table you can keep it as disconnected or connect it to the relevant dimensions. Either way will work if you design your DAX correctly.

[Kenaurec]

Okay thanks - took me longer than I want to admit but I've got a second date table now with Holidays and Weekends removed  that has a running index that I'm using to track business days - so  adding or subtracting from it will move me the correct amount now. I kept trying to calculate  the difference for each  Date  but I don't know how to do that  efficiently in Power Query.

If you don't mind explaining further, how would you do the calculation/steps in Power Query?   Would I just create 21 custom columns (date -1, date -2,etc.) and then Unpivot  to a single column? Would you convert the dates to numbers, create a list of all dates {..} and then Use the  group by feature to label them with an index? Would you use a custom function?

What steps would you have applied to your query to quickly calculate the # of business days back for each date so it would look like example table you had?