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.
Hi Team - I need to calculate the total number of business days between two dates. Attached the file and the results. Please help.
Solved! Go to Solution.
Hi @Shankar,
You can try to use below calculate column formula to calculate the total normal workday.(except saturday sunday)
Total WorkDays = COUNTROWS ( FILTER ( ADDCOLUMNS ( CALENDAR ( [Start], [End] ), "Day of Week", WEEKDAY ( [Date], 1 ) ), [Day of Week] <> 1 && [Day of Week] <> 7 ) )
If you need to remove other holidays, you need to create a holiday table and remove them in the formula.
Regards,
Xiaoxin Sheng
Hello, could someone show me how to remove holidays as well?
Hi @bengisby,
If you have other specific holiday need to remove for current date range, you can try to use except function to compare with current visual calendar table. My formula will remove weekend dates from virtual calendar table.
Other reference link:
Regards,
Xiaoxin Sheng
@v-shex-msft, this link is great, but I'm just learning and don't understand how to insert the measure result as a value in the new column... I have a business days value for each row of data, and so I need each row to also remove the holidays.
Hi @Shankar,
You can try to use below calculate column formula to calculate the total normal workday.(except saturday sunday)
Total WorkDays = COUNTROWS ( FILTER ( ADDCOLUMNS ( CALENDAR ( [Start], [End] ), "Day of Week", WEEKDAY ( [Date], 1 ) ), [Day of Week] <> 1 && [Day of Week] <> 7 ) )
If you need to remove other holidays, you need to create a holiday table and remove them in the formula.
Regards,
Xiaoxin Sheng
"If you need to remove other holidays, you need to create a holiday table and remove them in the formula."
Maybe you can show me?
I write Same Dax Function but it is showing The start date or end date in Calendar function can not be Blank value.
is ther any function to remove that error.
Thanks a lot Sir. It works .
Was looking for it badly.
Thanks,
Shankar A
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |