Chart (lines and bars) with cumulative sum based on two different dates

[massotebernoull]

Hello, 

I'm strugling trying to create a measure based on two type date columns for a chart.

I have a table with those facts:

opportunity	status	created on (DD/MM/YYYY)	oppotunity closed on (DD/MM/YYYY)
A	declined	01/01/2021	10/06/2021
B	declined	15/01/2021	24/11/2021
C	approved	20/01/2021	06/08/2021
D	declined	03/02/2021	19/03/2021
E	declined	03/02/2021	06/04/2021
F	declined	20/03/2021	07/08/2021
G	approved	25/03/2021	07/07/2021
H	declined	04/04/2021	23/10/2021
I	declined	11/04/2021	15/07/2021
J	declined	11/04/2021	12/06/2021
K	declined	30/04/2021	30/09/2021
J	approved	01/05/2021	04/10/2021
M	approved	07/05/2021	27/08/2021
N	declined	09/05/2021	10/11/2021
O	declined	17/06/2021	22/11/2021
P	declined	21/06/2021	15/11/2021
Q	approved	29/06/2021	15/11/2021
R	in negociation	15/08/2021	null
S	in negociation	20/08/2021	null
T	in negociation	23/08/2021	null

this is related with a Calendar Table based on 'oppotunity closed on'. To make the math easier, this Calendar have a CurrentMonthOffset Column, like a countdown to actual month. Like this:

Date	Current Month Offset
29/09/2021	-2
30/09/2021	-2
...	...
28/10/2021	-1
29/10/2021	-1
30/10/2021	-1
31/10/2021	-1
01/11/2021	0
02/11/2021	0
03/11/2021	0
04/11/2021	0

I want to create a chart like this:

the cumulative sum of opportunities must be based on column "created on", and the cumulative approved must be based on "opportunity closed on". I'm creating this chart based on Calendar month. The sum of opportunities approved is OK, but I'm not getting a way to create the cumulative sum of all opportunities.

The measure I used to create the opportunities approved is:

CumSum (approved) = 
CALCULATE(COUNT('Table'[opportunity]),
    FILTER(All('Table'), 'Table'[oppotunity closed on (DD/MM/YYYY)] <= MAX('Table'[oppotunity closed on (DD/MM/YYYY)])))

I need help to create the cumulative sum of all opportunities. Can anyone help with this?

[Ashish_Mathur]

Hi,

Share the download link of your PBI file and show the expected result in a Table format.

[v-eqin-msft]

Hi @massotebernoull ,

Not very clear. 

Did you mean that your CumSum(approved) measure did not return the expected result?

Can you please share more detail information to help us clarify your scenario?

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.