Improper calculation for non closed items within a month

[jbrolland]

current behavior

I feel like I am overcomplicating my table but here is the graph I am generating today

However the Open Vs Closed (orange) is not correct for 2021-06.

It should be 17 (75-58) instead of 14.

Here is my table

STATUS OF THE ACTION PLAN	Open Date	Closed Date	Opened Year and Month	Closed Year and Month	Opened by Month	Closed by Month	Open or Close	Opened Vs Closed
DONE	18-Jan-21	12-Feb-21	2021-01	2021-02	21	15	2021-02	6
ONGOING	12-Jan-21		2021-01	Not closed	21	0	2021-01	0
DONE	12-Jan-21	12-Feb-21	2021-01	2021-02	21	15	2021-02	6
DONE	12-Jan-21	29-Jan-21	2021-01	2021-01	21	4	2021-01	17
DONE	12-Jan-21	05-Mar-21	2021-01	2021-03	21	32	2021-03	-11
ONGOING	12-Jan-21		2021-01	Not closed	21	0	2021-01	0
DONE	12-Jan-21	23-Apr-21	2021-01	2021-04	21	48	2021-04	-27
DONE	12-Jan-21	26-Feb-21	2021-01	2021-02	21	15	2021-02	6
DONE	12-Jan-21	05-Mar-21	2021-01	2021-03	21	32	2021-03	-11
DONE	21-Jan-21	29-Jan-21	2021-01	2021-01	21	4	2021-01	17
DONE	12-Jan-21	05-Feb-21	2021-01	2021-02	21	15	2021-02	6
ONGOING	21-Jan-21		2021-01	Not closed	21	0	2021-01	0
DONE	25-Jan-21	05-Feb-21	2021-01	2021-02	21	15	2021-02	6
DONE	28-Jan-21	29-Jan-21	2021-01	2021-01	21	4	2021-01	17
DONE	20-Jan-21	26-Feb-21	2021-01	2021-02	21	15	2021-02	6
DONE	15-Jan-21	12-Feb-21	2021-01	2021-02	21	15	2021-02	6
DONE	12-Jan-21	29-Jan-21	2021-01	2021-01	21	4	2021-01	17
DONE	29-Jan-21	17-Feb-21	2021-01	2021-02	21	15	2021-02	6
ONGOING	19-Jan-21		2021-01	Not closed	21	0	2021-01	0
DONE	29-Jan-21	18-Mar-21	2021-01	2021-03	21	32	2021-03	-11
DONE	29-Jan-21	22-Mar-21	2021-01	2021-03	21	32	2021-03	-11
DONE	22-Feb-21	19-Mar-21	2021-02	2021-03	39	32	2021-03	7

....... cannot put the whole table unfortunately, here is the end to understand what is going on

STATUS OF THE ACTION PLAN	Open Date	Closed Date	Opened Year and Month	Closed Year and Month	Opened by Month	Closed by Month	Open or Close	Opened Vs Closed
DONE	30-Apr-21	30-Apr-21	2021-04	2021-04	66	48	2021-04	18
DONE	21-Apr-21	22-Apr-21	2021-04	2021-04	66	48	2021-04	18
ONGOING	26-May-21		2021-05	Not closed	72	0	2021-05	0
DONE	12-May-21	21-May-21	2021-05	2021-05	72	55	2021-05	17
DONE	13-May-21	28-May-21	2021-05	2021-05	72	55	2021-05	17
ONGOING	17-May-21		2021-05	Not closed	72	0	2021-05	0
ONGOING	06-May-21		2021-05	Not closed	72	0	2021-05	0
DONE	28-May-21	04-Jun-21	2021-05	2021-06	72	58	2021-06	14
ONGOING	01-Jun-21		2021-06	Not closed	75	0	2021-06	0
ONGOING	02-Jun-21		2021-06	Not closed	75	0	2021-06	0
ONGOING	03-Jun-21		2021-06	Not closed	75	0	2021-06	0

The first 3 columns are not computed, the rest is and here are the formula per column

Opened Year and Month = FORMAT(DATE(YEAR([Open Date]),MONTH([Open Date]),1),"YYYY-MM")

Closed Year and Month = IF(ISBLANK('Incidents follow-up with Paris'[Closed Date]), "Not closed", FORMAT(DATE(YEAR([Closed Date]),MONTH([Closed Date]),1),"YYYY-MM"))

Opened by Month = 
CALCULATE (
 Count('Incidents follow-up with Paris'[Opened Year and Month]),
 ALL('Incidents follow-up with Paris'),
 'Incidents follow-up with Paris'[Opened Year and Month] <= EARLIER ( 'Incidents follow-up with Paris'[Opened Year and Month] )
)

Closed by Month = 
IF ('Incidents follow-up with Paris'[Closed Year and Month] = "Not Closed", 0,(CALCULATE (
 Count('Incidents follow-up with Paris'[Closed Year and Month]),
 ALL ( 'Incidents follow-up with Paris'),
 'Incidents follow-up with Paris'[Closed Year and Month] <= EARLIER ( 'Incidents follow-up with Paris'[Closed Year and Month])
)))

Open or Close = IF([Closed Year and Month] = "Not Closed", 'Incidents follow-up with Paris'[Opened Year and Month], 'Incidents follow-up with Paris'[Closed Year and Month])

Opened Vs Closed = if('Incidents follow-up with Paris'[Closed by Month] = 0, 0, [Opened by Month] - [Closed by Month])

My graph for Open Vs Close will take Maximum of Open Vs CLosed so it is going to take the 4th row starting from last cause there is no item closed in June yet ending up to 14.

expected behavior

Able to get the proper calculation either in table or in the graph for June i.e 17 (75 - 58) even if nothing is closed in June

Solution ( I am not able to reply to @AllisonKennedy .... so I post my reply here)

Hi

I am not familiar with measure and had to scratch my head to find the formula but thanks for your advice @AllisonKennedy , it helped me to solve it:

I added MEASURES indeed with below formula (I removed the column Opened vs Closed first)

Opened vs Closed = MAX('Incidents follow-up with Paris'[Opened by Month] ) - MAX('Incidents follow-up with Paris'[Closed by Month] )

[AllisonKennedy · Accepted solution]

@jbrolland  You may need to do these as MEASURES not columns because in your calculated table, the value for 6 June is 72 (not 75) because the Opened Year and Month is still in May... 

[jbrolland]

Thanks I manage to find the solution thanks to your advice, please check my initial post casue there are too many restrictions on replying for unknown reason (I guess because a copy paste it s really frustrating and time consuming).