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.
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
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 |
---|---|
107 | |
98 | |
78 | |
65 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |