Help calculate and visualize in process days

[gcompliance]

Hello, I have two date columns Case Opened Date and Case Closed Date. I looking to figure out a way to show when an item was in process. Ideally we would like to be able to say for the month of Januaray 2020 we had x items in process. The logic I tossed around was:

For every calendar day (from day of first case – today):

                For each unique case:

                                If (today’s date >= case open date) & (today’s date < case closed date)

                                                Then open case count = open case count + 1

                                If (today’s date >= case open date) & (today’s date > case closed date)

                                                Then close case count = close case count + 1

I am not confident in my PowerBi skills to execute this properly. 

[v-yuaj-msft · Accepted solution]

Hi @gcompliance ,

I have modified the following measure.

close case count = COUNTROWS(FILTER(ALL('Items'),TODAY()>=[A-StartDate]&&TODAY()>[A-EndDate]&&NOT(ISBLANK([A-EndDate]))))

Result:

Hope that's what you were looking for.

Best Regards,

Yuna

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

[v-yuaj-msft]

Hi @gcompliance ,

Based on your description, you can create two measures as follows.

My test table:

open case count = COUNTROWS(FILTER(ALL('Items'),TODAY()>=[A-StartDate]&&TODAY()<[A-EndDate]))

close case count = COUNTROWS(FILTER(ALL('Items'),TODAY()>=[A-StartDate]&&TODAY()>[A-EndDate]))

Result:

Hope that's what you were looking for.

Best Regards,

Yuna

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

[gcompliance]

@amitchandak 

I think I got closer with the help of your post. 

I created an CalenderAuto and used this 

Active Cases = CALCULATE(COUNTx(FILTER(Query1, Query1[Opened]<=max('CalendarAuto'[Date]) && (ISBLANK(Query1[Closed]) || Query1[Closed]>max('CalendarAuto'[Date]))),(Query1[Case Number])),CROSSFILTER(Query1[Opened],'CalendarAuto'[Date],None))

I am not getting the correct response. As of today March 22 we have 52 active cases. The function above does not come to that conclusion. I also want to be able to look back and say on March 22, 2020 how many active cases did we have on that day.

[gcompliance]

Do you think this will work if the close date is blank?

Open Date 12/1/2020 

Closed Date null

because the case has not closed yet.

[amitchandak]

@gcompliance , refer if this can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...