How to calculate date difference excluding weekends except if there is a value

[Anonymous]

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...(....,

DATEDIFF( creation date, confirmation date, HOUR) - (CALCULATE

(Countrows('Date'), 'Date'[Workday]=0, DATESBETWEEN('Date'[Date], creation date, confirmation date)

)*24

)))

how can i tranform it into: "if there are values(creation or confirmation dates) on a saturday, than consider this day as a workingday, otherwise its still a weekend"

Thanks for your help! 

[v-janeyg-msft · Accepted 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.

[Anonymous]

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 

cycle time =

if( ISBLANK('Data'[confirmation date 1]), 

if('Data'[confirmation date 2]>='Data'[creation] 

&&

DATEDIFF('Data'[creation],'Data'[confirmation date 2],HOUR) - (

if(RELATED(Date[Weekday])=6, calculate (countrows('Date'), Date[Weekday]=7, DATESBETWEEN('Date'[Date], 'Data'[creation], 'Data'[confirmation date 2]))*24, calculate (Countrows('Date'), 'Date'[Workday]=0, DATESBETWEEN('Date'[Date], 'Data'[creation], 'Daten neu'[Confirmation date 2])

)*24

)>=0 && not(ISBLANK('Data'[confirmation date 2]))),

DATEDIFF('Data'[creation],'Data'[confirmation date 2],HOUR) - (

if(RELATED(Date[Weekday])=6, calculate (countrows('Date'), Date[Weekday]=7, DATESBETWEEN('Date'[Date], 'Data'[creation], 'Data'[Confirmation date 2]))*24, CALCULATE

(Countrows('Date'), 'Date'[Workday]=0, DATESBETWEEN('Date'[Date], 'Data'[creation], 'Data'[Confirmation date 2])

)*24

)), 

BLANK()),

if( 'Data'[Creation date 1]>'Data'[creation date 2],

DATEDIFF('Dats'[creation],'Data'[confirmation date 1],HOUR) - ( 

if(RELATED(Date[Weekday])=6, calculate (countrows('Date'), Date[Weekday]=7, DATESBETWEEN('Date'[Date], 'Data'[creation], 'Data'[Confirmation date 1]))*24,

CALCULATE

(Countrows('Date'), 'Date'[Workday]=0, DATESBETWEEN('Date'[Date], 'Data'[creation], 'Data'[Confirmation date 1])

)*24)),

DATEDIFF('Data'[creation],'Data'[confirmation date 2],HOUR) - ( if(RELATED(Date[Weekday])=6 , calculate (countrows('Date'), Date[Weekday]=7, DATESBETWEEN('Date'[Date], 'Data'[Creation], 'Data'[confirmation date 2]))*24,

CALCULATE

(Countrows('Date'), 'Date'[Workday]=0, DATESBETWEEN('Date'[Date], 'Data'[Creation], 'Data'[Confirmation date 2])

)*24

))))

I really don't know how to simplify and do it correct 

[v-janeyg-msft]

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

[Anonymous]

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...(....,

DATEDIFF( creation date, confirmation date, HOUR) - (if confirmation date or creation date are 'date'[Weekday]=6 and not blank, calculate weekdays 1to 6 (including weekday=6), otherwise CALCULATE

(Countrows('Date'), 'Date'[Workday]=0, DATESBETWEEN('Date'[Date], creation date, confirmation date)

)*24

)))

I just down know if something like that is possible and how to write it. 

[amitchandak]

@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