KPI, average without filter Cases Open

[Lyvili]

Good afternoon!

I am trying to calculate a KPI which is easy but due to a year month filter I am not able to reach it.

KPI is number of cases open at the end of the month.

Cases Table is linked to date calendar through Dimdateclose of the dimcases, that means when I use calendar date as main filter (as I have more sources), of course, is taking all the cases closed.

I tried with:

Cases Open = CALCULATE(SUM(DimCases.Cases), DimCases.Status = "Open", ALL(DimCases.DateClosed)).

It does not work 😞

Could you please help me?

Thanks in advance!

[Lyvili]

Thanks all! Before applying I am looking forward for a solution in a issue of the data. I will keep you informed.

[v-yaningy-msft]

Hi, @Lyvili 

According to your description, you want to filter all open cases based on Year Month filter, you can try to use @ray_aramburo method and if it doesn't work, you can share the pbix file without sensitive data to help solve your problem faster.

Best Regards,
Yang
Community Support Team

[ray_aramburo]

Assuming you have the right relationships and data model, you only need this portion of the measure:

Cases Open = CALCULATE(SUM(DimCases.Cases), DimCases.Status = "Open")

It will return you the number of open cases in the time span that you decide when you filter the date table.