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,
I have two dates columns and I want to calculate the number of working days between those two dates? like network days in excel.
How can we do this here in power BI?
Thanks.
Solved! Go to Solution.
Assume you have a dataset as below. We can create a calendar table and add a column in it to mark the working days. Then create a column in NetWorkDays table to show the number of working days.
DimDate = CALENDAR ( MIN ( NetWorkDays[Start date] ), MAX ( NetWorkDays[End date] ) ) WorkingDay_Mark = VAR WeekDayNum = WEEKDAY ( DimDate[Date] ) RETURN ( IF ( OR ( OR ( WeekDayNum = 1, WeekDayNum = 7 ), RELATED ( Holidays[Date] ) <> BLANK () ), FALSE (), TRUE () ) )
WorkingDay_Num = COUNTROWS ( FILTER ( DimDate, AND ( AND ( DimDate[Date] >= NetWorkDays[Start date], DimDate[Date] <= NetWorkDays[End date] ), DimDate[WorkingDay_Mark] ) ) )
Assume you have a dataset as below. We can create a calendar table and add a column in it to mark the working days. Then create a column in NetWorkDays table to show the number of working days.
DimDate = CALENDAR ( MIN ( NetWorkDays[Start date] ), MAX ( NetWorkDays[End date] ) ) WorkingDay_Mark = VAR WeekDayNum = WEEKDAY ( DimDate[Date] ) RETURN ( IF ( OR ( OR ( WeekDayNum = 1, WeekDayNum = 7 ), RELATED ( Holidays[Date] ) <> BLANK () ), FALSE (), TRUE () ) )
WorkingDay_Num = COUNTROWS ( FILTER ( DimDate, AND ( AND ( DimDate[Date] >= NetWorkDays[Start date], DimDate[Date] <= NetWorkDays[End date] ), DimDate[WorkingDay_Mark] ) ) )
When creating a new column using the below code, this method equates to an #ERROR (see attached screenshot)
WorkingDay_Num = COUNTROWS ( FILTER ( DimDate, AND ( AND ( DimDate[Date] >= NetWorkDays[Start date], DimDate[Date] <= NetWorkDays[End date] ), DimDate[WorkingDay_Mark] ) ) )
Were your columns on the 'NetworkDays' table named [Start Date] & [End Date] DAX/Measures?
Is there any reason why I can only choose measures?
What need to do if we mark like monday is holiday ?
in the ablove code
Do you have a 'Working Day' marker or identifier in your Date Dimension table?
If so, you can use this to mark Monday's as a non-working day and it can then be excluded from future calculations.
Please have alook at this, it may answer your question:
http://www.powerpivotpro.com/2012/11/networkdays-equivalent-in-powerpivot/
Can you try something like:
=CALCULATE(SUM(Dates[IsWorkday]), DATESBETWEEN(Dates[Date], TwoDates[Column1], TwoDates[Column2] ) )
Please have alook at this, it may answer your question:
http://www.powerpivotpro.com/2012/11/networkdays-equivalent-in-powerpivot/
Can you try something like:
=CALCULATE(SUM(Dates[IsWorkday]), DATESBETWEEN(Dates[Date], TwoDates[Column1], TwoDates[Column2] ) )
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 |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |