Calculating NETWORKDAYS between timestamps based on multiple conditions

[BalazsNy]

Hi All,

I have 2 table with Many-to-one relationship based on Record Number. One of the table is a history table including timestamps for all the steps (& actions) taken on each record. So 1 record can have even +50 rows in history table (this is the many part of relationship).

Small extract from History table:

I would like to define the NETWORKDAYS elapsed between 1st "Send proposal to Client" date (2021-10-18 13:47:16.089) and the last "Requirement definition" date (2021-09-29 14:32:18.734). Thus, only the highlighted area counts.

I have already calculated these 2 dates with a measure & calculated the NETWORKDAYS in between them, but I also have to take out the period while the record was on hold (see Actions taken: 2021-09-29 14:35:01.233 (Put on Hold) - 2021-10-18 10:11:32.489 (remove from on Hold)). In case of this example, the record (KMD-REQ-001573), was put on hold only once during the highlighted period, but it can happen that the record is put on hold multipe times.

So the aim is to track & take out all the periods from NETWORKDAYS while the record was on hold between last "Requirements definition" date & 1st "Send proposal to client date" (network days: 2021-09-29 14:32:18.734-2021-10-18 13:47:16.089 minus on hold period: 2021-09-29 14:35:01.233 - 2021-10-18 10:11:32.489)

Could you please suggest a formula to solve this issue?

Thanks in advance!

[Anonymous]

Hi @BalazsNy ,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[BalazsNy]

Dear @Anonymous (Xiaoxin),

I'm afraid I do not have permission to attach pbix file... 😕