Hoping for some education here. What is a simple Excel COUNTIF is eluding me in PBI.
I am creating a Line and Stacked Column chart to show case status within a given time period (week end or month). I get the per week data correctly set but the line formulas/measures are not as expected.
The columns display how many cases were opened/closed per week but I want the line to show the running total with the Status "Open". Here's an example. (Week end date is referenced from another table in my source data)
The Stacked columns show (with a filter of closed for example) 5 for 'week end' 4/5/2019 and 2 for 4/12/2019. 'Week end' would be my X axis in this example.
I want the line to show a running total of Open cases.
In this example, it would show 6 for 4/5/2019 and 8 for 4/12/2019 being the running total of open cases.
I believe this can be accomplished through measures instead of new column creation but please correct me. Which COUNT functions should I be looking at here? Rows counts adding filters?
I've read the various DAX COUNT variations multiple times and I'm missing something..
Thank you Parry for that speedy response. You get the solution acceptance as you delivered exactly what I requested. (I had essentially the same measure (with the exception of the last line which I will go research for future).
You also made me realize why the chart was not looking the way it had within Excel. The file contains all of the historical data since the inception of it and that is what was being referenced.
In the case I stated, it would show the current status of all cases (and again you delivered the solution) but the status changes as cases are closed out over time and new ones are opened. I need to figure out how to pull the historical data in, retain it (new table I assume), and also generate the new numbers moving forward. The last 'Week End' count generated from your measure would be the one I want to keep.
That way, in a year, I can still see that on week ending 4/12/2019 we had 8 open case and on 4/*/2020 we had 10 and so forth.
Ok, more work needed to turn this into an automated and interactive PBI dashboard!