Dax to find running total of open cases?

[bryn987]

Hoping someone can point me to a good resource that I could use to create the following report.

I'm tyring to build a report so I can see daily, the number of open cases I have.  For example:

On Monday, I had 1,000 cases that were Open, New, Waiting on Info and Work in Progress.

On Tuesday, 100 of those cases turned to Resolved or Closed but we gained 500 New cases.

On Wednesday, 300 cases turned to Resolved or Closed but we gained 50 New cases.

I would like to be able to show a report that has the data like this:

Monday - 1,000 open cases

Tuesday - 1,400 open cases

Wednesday - 1,150 open cases

This will allow me to trend on our open cases, whether the number is getting higher or lower.  I do have a calendar table and my data fields are:

• Created
• Resovled
• Assigned On
• State

Any help would be appreciated!  Thank you

[Anonymous · Accepted solution]

Hi @bryn987 ,

My idea is to create an unconnected calendar table as the x-axis and then create a measure like this one.

running total of open cases = 
VAR _date = SELECTEDVALUE('Calendar'[Date])
VAR _created = CALCULATE(COUNTROWS('Table'),'Table'[Created]<=_date)
VAR _resovled = CALCULATE(COUNTROWS('Table'),'Table'[Resovled]<=_date&&'Table'[Resovled]<>BLANK())
VAR _result = _created - _resovled
RETURN
_result

Then create a line chart using the 'calendar' [date] as the X-axis and the measure as the Y-axis. Please refer to the sample file.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[bryn987]

That worked fantastic and was quite simple.  Thank you for assisting me!

[vicky_]

Power BI has a built in Waterfall Chart that could meet your requirements of visualising the running totals. I imagine that the "category" would have a date, the breakdown would be the state and the y-axis is the count of cases.

More info here: https://learn.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-waterfall-charts?tabs=powe...