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.
Solved! Go to Solution.
Hi @Lihobday,
Smoupre's solution seems well, you can also take a look at below formula, I add the condtion to replace blank value to current date.
WorkDay Count = COUNTROWS ( FILTER ( ADDCOLUMNS ( CALENDAR ( [Assigned date], IF ( [Actual briefing date] <> BLANK (), [Actual briefing date], TODAY () ) ), "DayofWeek", WEEKDAY ( [Date], 1 ) ), [DayofWeek] <> 1 && [DayofWeek] <> 7 ) )
Original table:
Result:
Regards,
Xiaoxin Sheng
Hi @Lihobday,
If 'brief date' and 'actual close date' both not blank, which one you'd like to calculate?
In my opinion, I will choose the smaller one as the end date parameter:
WorkDay Count = VAR closedate = MIN ( [Actual close date], [Actual briefing date] ) RETURN COUNTROWS ( FILTER ( ADDCOLUMNS ( CALENDAR ( [Assigned date], IF ( closedate <> BLANK (), closedate, TODAY () ) ), "DayofWeek", WEEKDAY ( [Date], 1 ) ), [DayofWeek] <> 1 && [DayofWeek] <> 7 ) )
Notice: min and max function will ignore blank value.
Regards,
Xiaoxin Sheng
Hi @Lihobday,
Smoupre's solution seems well, you can also take a look at below formula, I add the condtion to replace blank value to current date.
WorkDay Count = COUNTROWS ( FILTER ( ADDCOLUMNS ( CALENDAR ( [Assigned date], IF ( [Actual briefing date] <> BLANK (), [Actual briefing date], TODAY () ) ), "DayofWeek", WEEKDAY ( [Date], 1 ) ), [DayofWeek] <> 1 && [DayofWeek] <> 7 ) )
Original table:
Result:
Regards,
Xiaoxin Sheng
Thank you for the solution XiaoXin Sheng! This work wonders!!! Had been figuring this out for a long time...!
Hi @Lihobday,
I'm glad to hear that my formula is helps for you.
Regards,
Xiaoxin Sheng
Hi v-shex,
I have another problem that require some help.
1. I have another column Close Date that has been added. How may i calculate by working days the difference between actual Close date and Brief date. The challenge is sometimes brief date field will be left blank. And i will get error from POWERBI "The start date or end date in Calendar Function can not be Blank value"
Is it possible to have a fomulae that calculates only if there is an entry in "Brief date" else it will not calculate?
Thank you!!
Hi @Lihobday,
If 'brief date' and 'actual close date' both not blank, which one you'd like to calculate?
In my opinion, I will choose the smaller one as the end date parameter:
WorkDay Count = VAR closedate = MIN ( [Actual close date], [Actual briefing date] ) RETURN COUNTROWS ( FILTER ( ADDCOLUMNS ( CALENDAR ( [Assigned date], IF ( closedate <> BLANK (), closedate, TODAY () ) ), "DayofWeek", WEEKDAY ( [Date], 1 ) ), [DayofWeek] <> 1 && [DayofWeek] <> 7 ) )
Notice: min and max function will ignore blank value.
Regards,
Xiaoxin Sheng
Create a custom column in your data table:
Calc Date = IF(ISBLANK([AssignedDate]),TODAY(),[AssignedDate])
Use that to calculate working days.
Use conditional formatting in a table visualization to show yellow and red values.
Thank you Smoupre!
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 |
---|---|
118 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |