Forum Discussion
Help calculate and visualize in process days
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.
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.
5 Replies
- amitchandak
Super User
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.
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.
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.
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.
