Counting days of cases on certain statuses

[Frunkle93]

Hi all,

I have been searching on this forum for some time, but couldn't find my specific issue.

The days for cases in my dataset should only be counted when the cases have certain status. 

When the Status = "Nieuw" or "In bewerking", I want to start counting days, and I want to stop or pause counting days when the case is on "Geagendeerd" or "Gesloten". 

A case can be closed ("Gesloten") and open up again. So, during the closed period (or "Geagendeerd" period) I don't want to count the days.

The rows in my set are often duplicated (or seem duplicated).

In the example below I only want to cumulate the days between October 22 (CreatedDate) and October 26, pause between October 26 and November 1st (because the case has status "Gesloten") and start counting again on November 1st, until November 8th. This can be determined by LastModifiedDate.

In my report table I want to display the CaseNumber with the number of days on status "Nieuw" or "In bewerking".

I hope I have been clear. 

Thanks in advance!

[v-yangliu-msft · Accepted solution]

Hi  @Frunkle93 ,

Here are the steps you can follow：

1. In Power query, Add Column – Index Column – From 1.

2. Create calculated column.

Rank =
RANKX(FILTER('Table',[Status] <> "Gesloten"&&[Status]<>"Geagendeerd"),[Index],,ASC,Dense)

Flag =
var _alllastrow=MAXX(ALL('Table'),[Index])
var _GesMin=CALCULATE(SUM('Table'[Index]),FILTER(ALL('Table'),[Status]<>"Gesloten"&&CALCULATE(MAX('Table'[Status]),FILTER(ALL('Table'),[Index]=EARLIER('Table'[Index])+1))="Gesloten"&&[Index]<>_alllastrow))
var _GesMax=CALCULATE(MAX('Table'[Index]),FILTER(ALL('Table'),[Status]<>"Gesloten"&&CALCULATE(MAX('Table'[Status]),FILTER(ALL('Table'),[Index]=EARLIER('Table'[Index])-1))="Gesloten"&&[Index]<>_alllastrow))
var _GeaMin=CALCULATE(MAX('Table'[Index]),FILTER(ALL('Table'),[Status]<>"Geagendeerd"&&CALCULATE(MAX('Table'[Status]),FILTER(ALL('Table'),[Index]=EARLIER('Table'[Index])+1))="Geagendeerd"&&[Index]<>_alllastrow))
return
SWITCH(
    TRUE(),
    [Index]<=_GesMin,_GesMin,
    [Index]>=_GesMax&&[Index]<=_GeaMin,_GeaMin-_GesMax+1,
    BLANK())

3. Result:

Best Regards,

Liu Yang

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

[Frunkle93]

CreatedDate is indeed the starting date.

The counting of days since CreatedDate should be paused when a case has the Status of "Geagendeerd" or "Gesloten".

[freginier]

If you just want to count with condition you can try to create a measure  

MEASURE = CALCULATE(COUNT([CaseNumber]),NOT([Status] in {"Geagendeerd","Gesloten"})

[Frunkle93]

Thanks for your answer.

I want to count the days a case is on certain statuses. I don't want to count the amount of cases.

[freginier]

What is the date column used for the calculation ? CreatedDate ? 

How do you define the pause ?