Forum Discussion
DAX DATESBETWEEN with Filter in Power Query
Hello!
I'm using this DAX function to get the number of working days between 2 Dates:
=#Days
CALCULATE(
COUNTROWS ('Calendar'),
DATESBETWEEN ('Calendar'[Date], Order[Date], TODAY() -1 ),
'Calendar'[IsWorkingDay] = TRUE(),
ALL ( Order)
)Is it possible to calculate this value without a second table in Power Query?
Why not do this as a DAX measure instead of a column (DAX or M/Query)? In any case, here is an M formula you can add to a custom column to calculate the # of working days before the date and yesterday. It's more involved than the DAX approach.
= List.Count(List.Select(List.Dates([SaleDate], Duration.TotalDays(Date.AddDays(Date.From(DateTime.LocalNow()), -1) - [SaleDate]), #duration(1,0,0,0)), each List.Contains({1,2,3,4,5}, Date.DayOfWeek(_))))
Pat
Hi joshua1990
Have you tried Pat's code? Based on my test, it should work in Power Query. You can add a custom column to get the working days assuming that working days are Monday to Friday in every week.
Regards,
Community Support Team _ Jing
2 Replies
Why not do this as a DAX measure instead of a column (DAX or M/Query)? In any case, here is an M formula you can add to a custom column to calculate the # of working days before the date and yesterday. It's more involved than the DAX approach.
= List.Count(List.Select(List.Dates([SaleDate], Duration.TotalDays(Date.AddDays(Date.From(DateTime.LocalNow()), -1) - [SaleDate]), #duration(1,0,0,0)), each List.Contains({1,2,3,4,5}, Date.DayOfWeek(_))))
Pat
Hi joshua1990
Have you tried Pat's code? Based on my test, it should work in Power Query. You can add a custom column to get the working days assuming that working days are Monday to Friday in every week.
Regards,
Community Support Team _ Jing