Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Dears,
I am new to Power BI, I have the dim date table inculde a column called "Working dayes" and the value is 1 or 0, the 1 is for working day and 0 for weekend.
I want to know the cuurent buiness day number for each day .
please check the bleow example:
Day | Working Day | I want to calculate this caloumn Working day number |
Thursday, November 1, 2018 | 1 | 1 |
Friday, November 2, 2018 | 0 | |
Saturday, November 3, 2018 | 1 | 2 |
Sunday, November 4, 2018 | 1 | 3 |
Monday, November 5, 2018 | 1 | 4 |
Tuesday, November 6, 2018 | 1 | 5 |
Wednesday, November 7, 2018 | 1 | 6 |
Solved! Go to Solution.
Hi @A_H
you need to create a month and year columns and then you can do:
= IF ( 'Table'[Working Day] = 0, BLANK (), CALCULATE ( SUM ( 'Table'[Working Day] ), ALLEXCEPT ( 'Table', 'Table'[Month], 'Table'[Year] ), 'Table'[Day] <= EARLIER ( 'Table'[Day] ) ) )
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Hi @A_H
you need to create a month and year columns and then you can do:
= IF ( 'Table'[Working Day] = 0, BLANK (), CALCULATE ( SUM ( 'Table'[Working Day] ), ALLEXCEPT ( 'Table', 'Table'[Month], 'Table'[Year] ), 'Table'[Day] <= EARLIER ( 'Table'[Day] ) ) )
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Thank you so much for this solution! I have been searching for over 9 hours.
Thanks
@A_H Please confirm that you want to flag working days i.e (Mon - Fri) and Sat - Sun are weekends. So you want to flag working days as 1 and weekends as 0.
Proud to be a PBI Community Champion
all dayes are working days except friday and the woking days coulmn is calculated what I need I want to know the current working day number in the current month.
@A_H Please try this as a "New Column"
WorkingDayNumber = VAR _Rnk = RANKX(FILTER(Test80WorkinDay,FORMAT([Date],"DDDD")<>"Friday" && FORMAT([Date],"MMYYYY") = FORMAT(EARLIER([Date]),"MMYYYY") ),[Date],[Date],ASC) RETURN IF(FORMAT([Date],"DDDD")="Friday",0,_Rnk)
Please Note - I've flagged all Fridays as 0 (As it is a non-working day)
Proud to be a PBI Community Champion
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
66 |