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.
Hello,
I have a factable and a Mastercalendar as a dimensiontable. In the Bookingtable I want to know the processingtime of
two Dates without the weekends and the non-productive Days. With Bookingdate - IncomeDate I would only get the "WrongProcessingTime" but I need the "CorrectProcessingTime".
In the Mastertable I have all the information, but I don't know how to combine these. The "ProductiveDay" column tells me if its
a working day or not. 1 is a ProductiveDay. A Zero tells me its weekend or public holiday.
I'm thinking of a calculated column like Datediff( IncomeDate, BookingDate, Day) , but I don't know how to include the ProductiveDays into that function.
BookingTable
IncomeDate | BookingDate | ID | WrongProcessingTime | CorrectProcessingTime | |||||
2021-09-01 | 2021-09-01 | 8888 | 0 | 0 | |||||
2021-09-02 | 2021-09-03 | 8889 | 1 | 1 | |||||
2021-09-03 | 2021-09-06 | 8890 | 3 | 1 | |||||
2021-09-06 | 2021-09-07 | 8891 | 1 | 1 | |||||
2021-09-07 | 2021-09-07 | 8892 | 0 | 0 | |||||
2021-09-08 | 2021-09-08 | 8893 | 0 | 0 | |||||
2021-09-09 | 2021-09-14 | 8894 | 5 | 3 | |||||
2021-09-10 | 2021-09-13 | 8895 | 3 | 1 |
|
Mastercalendar:
Date | Weekday | ProductiveDay |
2021-09-01 | Wednesday | 1 |
2021-09-02 | Thursday | 1 |
2021-09-03 | Friday | 1 |
2021-09-04 | Saturday | 0 |
2021-09-05 | Sunday | 0 |
2021-09-06 | Monday | 1 |
2021-09-07 | Tuesday | 1 |
2021-09-08 | Wednesday | 1 |
2021-09-09 | Thursday | 1 |
2021-09-10 | Friday | 1 |
2021-09-11 | Saturday | 0 |
2021-09-12 | Sunday | 0 |
I really appreciate your help.
Thank you very much in advance.
Best.
Solved! Go to Solution.
@Applicable88
Please check the reply here:
https://community.powerbi.com/t5/Power-Query/Networkdays-between-two-dates-dynamically/m-p/2099591
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Applicable88
Please check the reply here:
https://community.powerbi.com/t5/Power-Query/Networkdays-between-two-dates-dynamically/m-p/2099591
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Applicable88 , You can try a new column like
business Day = COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(Table[IncomeDate],Table[Booking Date]),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1))
How to calculate Business Days/ Workdays, with or without date table: https://youtu.be/Qv4wT8_P-AA
HI @amitchandak,
the calendar part is giving me an error: the startdate of the calendar function cannot be after the enddate...
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 | |
94 | |
80 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |