Count Days If Working Day

[rohi__]

Hello everyone,

I couldn't find a solution for my problem so I created my own Question.

I'd like to get the amount of days which expired during the period "StartDate" to "EndDate" if it was a working day.

So I got my Calendar with:

Date | Workday

1/1/2019 | 0

1/2/2019 | 0

1/3/2019 | 1

1/4/2019 | 1

1/5/2019 | 1

1/6/2019 | 0

And the Orders Table:

Order | StartDate | EndDate

A | 1/1/2019 | 1/6/2019

B | 1/3/2019 | 1/4/2019 

I'd like to create a measure or a column in the orders table.

Is there something like "CountDays(From = StartDate, To = EndDate, IF ( Workday = 1))"?

Thanks in Advance!

Regards,

rohi__

[rohi__]

Thanks for your answer Vegar!

I tried the logic behind your Code, it's working. But the performance is really bad... The code ran the whole night..

Is there another way?

Thanks for your reply!

[Channa]

try to crete variable and pass working days into it

networkingdays(start,end,variable)

[JordyWegman · Accepted solution]

Hi,

This is your solution. You can use IntervalMatch for this. Try the following script:

[YourData]:
Load * Inline [
Date , Workday
1/1/2019 , 0
1/2/2019 , 0
1/3/2019 , 1
1/4/2019 , 1
1/5/2019 , 1
1/6/2019 , 0
];

[Data]:
Load * Inline [
Order , StartDate , EndDate
A , 1/1/2019 , 1/6/2019
B , 1/2/2019 , 1/4/2019 
];

Inner Join IntervalMatch (Date)
Load
StartDate,
EndDate
Resident Data;

Then add the following formula:

Count({$< Workday = {1}>}Workday)

Results in the following:

Jordy

Climber

[Vegar · Accepted solution]

Sure there is another way to solve this. Lets try using IntervalMatch().  See attached qvf

Script using intervalMatchResult in table using different total methods

 Ping: @Den

[Vegar]

It looks like me and @JordyWegman thought of a similar approach. The main difference between our two solutions is how many days that should be included in the interval. In Jordy's all days including start and end date are included, in my the first day is excluded (requirement in the similar post by @ Den. )

[JordyWegman]

Yes, good solution @Vegar, that depends on the whishes of the rohi.

Jordy

Climber

[rohi__]

Thank you guys @JordyWegman  @Vegar  for your help!

I will implement the codes soon and will mark your answers as solution!

Thanks again!

Regards