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 everyone!
I need some help calculating the date difference between two dates.
I am trying to calculate the cycle time with different conditions. So usually its the date difference between creation and confirmation date without weekends, as nobody is working on sat&sun. Now there are some saturdays that are exceptionally working days and have to be taken into the calculation of the cycle times.
My formula without any conditions that i habe to consider is like following:
if(... if...(....,
Solved! Go to Solution.
Hi, @Anonymous
Your idea is completely achievable, and it is not difficult, but I don't know what your data is like. Can you share some sample data, such as what is in the workday, weekday and holiday? So we can help you modify the formula.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Anonymous
Your idea is completely achievable, and it is not difficult, but I don't know what your data is like. Can you share some sample data, such as what is in the workday, weekday and holiday? So we can help you modify the formula.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hey @v-janeyg-msft , sorry I was out of office
so the data is basically like in that table, the other informartions are not really relevant:
As there are confirmation & creation dates on saturday, this saturday was a working day.
There are also some restrictions so i will post my formula here, too.
FYI my date table is related to the confirmation date 2
TO number | Item | Creation date | Confirmation date 1 | Confirmation date 2 | Cycle time in hours | comment |
12345678 | 1 | 25 june 2021 | 28 june 2021 | here its not recognizable that saturday was a working day, so it calculates the cylce time only Friday & Monday | ||
12345679 | 5 | 26 june | 26 june | Confirmation date 2 is a saturday so the calculation is correct | ||
12345689 | 1 | 26 june | 28 june | Confirmation date is a monday so the calculation is false even though it was created on a saturday | ||
12345789 | 3 | 24 june | 26 june | confirmation date is saturday -> working day, correct |
hope that's understandable.
And here is my super complicated formula with all the restrictions
Hi, @Anonymous
I really want to help you. But, from the little sample data and comments you gave, the calculation logic isn't described clearly, and I don’t know how to judge when to add Saturday and when not to add. And I don’t know what the result you want, so...If you still need help, I think what you need to provide is part of the sample data that can be tested, as well as the calculation logic, and the desired result instead of the long code.
Reference:
How to Get Your Question Answered Quickly - Microsoft Power BI Community
Best Regards
Janey Guo
Thank you for your answer.
Maybe i should have mentioned that i already have a date table with columns 'date', 'weekday', 'holiday' and 'workday'.
so i need somehow to use an if function like that:
if(... if...(....,
I just down know if something like that is possible and how to write it.
@Anonymous , You can get workday like
Work Day = COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(Table[Creation date],Table[confirmation 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
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 |
---|---|
113 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |