calculate sum of Active Cases ignoring Axis

[Anonymous]

Hello Community!

Have a question, how to calculate sum of Active Cases ignoring Axis.

So basically in one column chart there should be number of cases per type and sum of all Active cases until each date.

I tried to use the following DAX:

Active Cases SUM =

var Active_Num =
COUNTROWS(FILTER('Incident Fact','Incident Fact'[statecodename] = "Active"))
RETURN
Calculate(Active_Num, All())

But i get something like that, basically the blue chart should show the total of all Active cases until the particular date:

However i need the following results:

Thanks in advance!

[Anonymous]

You can find the expected result below:

dummy sample could be following:

Case number	Casetype	Date
1	Active	01.02.2022
2	Resolved	02.02.2022
3	Canceled	03.02.2022
4	Active	01.02.2022
5	Resolved	02.02.2022
6	Canceled	03.02.2022
7	Active	01.02.2022
8	Resolved	02.02.2022
9	Canceled	03.02.2022
10	Active	01.02.2022
11	Resolved	02.02.2022
12	Canceled	03.02.2022
13	Active	04.02.2022
14	Resolved	05.02.2022
15	Canceled	04.02.2022
16	Resolved	05.02.2022
17	Canceled	04.02.2022
18	Active	05.02.2022

Cheers

RT

[lbendlin]

I don't see how to get from the sample data to the expected outcome. There seem to be a couple of columns missing in the sample data?

[Anonymous · Accepted solution]

I think i found the solution with the DAX below:

Active Cases Sum =

CALCULATE(

[Active Cases],

FILTER(

ALL( 'incident Fact' ),

'Incident Fact'[CreatedOn_Date] <= MAX( 'Incident Fact'[CreatedOn_Date])

)

[Anonymous]

Hello Ibdendlin,

do you have an idea how it can be adjusted?

Regards,

RT

[lbendlin]

Please provide sanitized sample data that fully covers your issue.

Please show the expected outcome based on the sample data you provided.

[lbendlin]

"chart should show the total of all Active cases until the particular date"

You may want to adjust your measure to make it cumulative.