Calculate number of days with IF condition

[Anonymous]

Hello.

I'm relatively new to PBI and DAX language and I'm having some difficulties finding a way to calculate the number of days when a certain condition happens.

In order to make it easier to understand, I'll post an example of what I'm trying to calculate.

For this example, I'm just using data for 1 process, but the excel has many more "Process_ID's" and subsequent information.

Process_ID	Appointment_ID	Appointment_Date	Incapacity_ID
221673	1189606	09/01/2019	ITA
221673	1189608	14/01/2019	ITA
221673	1189611	18/01/2019	ITA
221673	1189924	21/01/2019	ITP
221673	1191665	23/01/2019	ITA
221673	1193025	05/02/2019	ITP
221673	1200313	08/02/2019	SI
221673	1201729	12/02/2019	ITA
221673	1219915	17/02/2019	ITP
221673	1304806	25/02/2019	ACSD

So, I need to calculate the number of days when the incapacity_ID is "ITA", but as you can see, there are a few dates in between that have "incapacity_ID" different than "ITA".

For this particular example, the final result should be 30 days of ITA... since that the first 3 appointments were given "ITA" we already have 12 days for this period (between the first date - 9/01/2019 - and the date where the ITA "ends" - 21/01/2019).

Then...on the 5th appointment, we got another ITA so this would add 13 more days (difference between the date ITA starts and ends, in this case starting date is 23/01/2019 and end date 05/02/2019). So, right now, we have 12 days from the first 3 appointments and 13 days from the 5th appointment.

Then, on 8th apppointment you can see that there is once again the ITA incapacity, so using the same logic as before, this would add up 5 more days ( days between 12/2/2019 and 17/02/2019),  resulting in a total of 30 days of ITA.

Now, my question is if there is any way I could write a DAX formula that makes this calculation automatically and for many more "Process_ID's"?

Thanks in advance.

[smpa01 · Accepted solution]

@Anonymous  I think I might have what you need

If you have a data like this

Process_ID	Appointment_ID	Appointment_Date	Incapacity_ID
221673	1189606	1/9/2019	ITA
221673	1189608	1/14/2019	ITA
221673	1189611	1/18/2019	ITA
221673	1189924	1/21/2019	ITP
221673	1191665	1/23/2019	ITA
221673	1193025	2/5/2019	ITP
221673	1200313	2/8/2019	SI
221673	1201729	2/12/2019	ITA
221673	1219915	2/17/2019	ITP
221673	1304806	2/25/2019	ACSD
221674	1189606	1/9/2019	ITA
221674	1189608	1/14/2019	ITA
221674	1189611	1/18/2019	ITA
221674	1189924	1/21/2019	ITP
221674	1191665	1/23/2019	ITA
221674	1193025	2/5/2019	ITP
221674	1200313	2/8/2019	SI
221674	1201729	2/12/2019	ITA
221674	1219915	2/17/2019	ITA
221674	1304806	2/25/2019	ITA

You can get the output by both as a calculated table or a measure following the attached.

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

You can add an index column first.

Use the if statement to calculate the next date corresponding to "ITA".

IS_ita = IF(MAX('Table'[Incapacity_ID])="ITA",CALCULATE(FIRSTNONBLANK('Table'[Appointment_Date],1),FILTER(ALLEXCEPT('Table','Table'[Process_ID]),'Table'[Index]=SELECTEDVALUE('Table'[Index])+1)))

Then use "DateDiff" to calculate the number of days between two dates:

datediff_ = DATEDIFF(MAX('Table'[Appointment_Date]),[IS_ita],DAY)

For more details,please refer to the pbix.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you so much!

Both solutions worked!  (from @smpa01  and @V-lianl-msft )

I found the solution from @V-lianl-msft a bit easier to understand though (probably because I'm still kinda new on PBI), nevetheless, I was wondering if you guys could help me with one more question....

Let's take the same example, but with a few differences...Let's say I have the following data:

221673	1189606	09/01/2019	ITA
221673	1189608	14/01/2019	ITA
221673	1189611	18/01/2019	ITA
221673	1189924	21/01/2019	ITP
221673	1191665	23/01/2019	ITA
221673	1193025	05/02/2019	ITP
221673	1200313	08/02/2019	SI
221673	1201729	12/02/2019	ITA
221673	1219915	17/02/2019	ITP
221673	1304806	25/02/2019	ACSD
221673	1389697	03/03/2019	ITA
221673	1474588	07/03/2019	SI
221673	1559479	15/03/2019	ACSD

As you can see, on 25/2/2019 there's and incapacity_ID named "ACSD"....Is there any way to calculate the number of days of ITA incapacity untill the first ASCD incapacity ID shows up? In this case we would have something like 30 days until the first ACSD  and 4 days until the second ASCD.

Basically, everytime I get an "ACSD", I need the calculation of ITA days to be reseted.

Is that possible?

Thanks once again for the help!

[smpa01 · Accepted solution]

@Anonymous  I think I might have what you need

If you have a data like this

Process_ID	Appointment_ID	Appointment_Date	Incapacity_ID
221673	1189606	1/9/2019	ITA
221673	1189608	1/14/2019	ITA
221673	1189611	1/18/2019	ITA
221673	1189924	1/21/2019	ITP
221673	1191665	1/23/2019	ITA
221673	1193025	2/5/2019	ITP
221673	1200313	2/8/2019	SI
221673	1201729	2/12/2019	ITA
221673	1219915	2/17/2019	ITP
221673	1304806	2/25/2019	ACSD
221674	1189606	1/9/2019	ITA
221674	1189608	1/14/2019	ITA
221674	1189611	1/18/2019	ITA
221674	1189924	1/21/2019	ITP
221674	1191665	1/23/2019	ITA
221674	1193025	2/5/2019	ITP
221674	1200313	2/8/2019	SI
221674	1201729	2/12/2019	ITA
221674	1219915	2/17/2019	ITA
221674	1304806	2/25/2019	ITA

You can get the output by both as a calculated table or a measure following the attached.

[smpa01]

@Anonymous did you try the solution yet. Please let me know if this is what you are looking for.

[Greg_Deckler]

Well, what I would do would be to create a calculated column using EARLIER like in my MTBF example. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...

[Anonymous]

I tried using the formula you used on your post, but it's not working. It's returning a syntax error.

I download the data set you shared in the example given and tried to apply the formula in there but it's also returning syntax error.