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.
Good Morning,
I have a problem and I don't know how to solve it.
I have 2 tables, the first with all the production data and the second with a single column where there are the days that are holidays and weekends. The days that are working days do not appear in that second table.
In the first table I have 2 fields that are dated, the first the Date when the order was placed, and the other when the product must be delivered to the customer.
The goal of everything, is that I have to get to know the number of working days between the 2 dates.
I already have the part of the difference between the dates, but I don't know how to subtract the number of Holidays that come from the other Table.
I have tried it with the COUNTROWS and FILTER functions, but I cannot solve it ...
Could you help me?
Thanks and greetings,
Solved! Go to Solution.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
// T1 with prod data: OrderDate, DeliveryDate
// T2 with hols and weekends: ExclusionDate
[Num Of Working Days] = // calculated column in T1
var __startDate = T1[OrderDate]
var __endDate = T1[DeliveryDate]
var __holidayAndWeekendCount =
COUNTROWS(
FILTER(
T2,
T2[ExclusionDate] >= __startDate
&&
T2[ExclusionDate] <= __endDate
)
)
var __result =
__endDate - __startDate
+ 1 - __holidayAndWeekendCount
return
__result
Best
D
// T1 with prod data: OrderDate, DeliveryDate
// T2 with hols and weekends: ExclusionDate
[Num Of Working Days] = // calculated column in T1
var __startDate = T1[OrderDate]
var __endDate = T1[DeliveryDate]
var __holidayAndWeekendCount =
COUNTROWS(
FILTER(
T2,
T2[ExclusionDate] >= __startDate
&&
T2[ExclusionDate] <= __endDate
)
)
var __result =
__endDate - __startDate
+ 1 - __holidayAndWeekendCount
return
__result
Best
D
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
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 |
---|---|
43 | |
23 | |
21 | |
15 | |
15 |
User | Count |
---|---|
45 | |
31 | |
30 | |
18 | |
17 |