Forum Discussion
Solved
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 Janua...
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.
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.
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.