How to DAX a cumulative calculation when addiig or deleting (with condition)?

[saranp780]

I have already done a normal cumulative report with this DAX 

Cumulative Licwnse = CALCULATE (COUNTA ( 'Cumulative'[Username] ),
FILTER (ALL ( 'Calendar'[Date] ),'Calendar'[Date] <= MAX ( 'Calendar'[Date] )))

Let get started on my logic.

Now, my new requirement is to add the new condition to update them. By the way, my logic is when I put the data in delete field that means this user is no longer anymore so next year the graph will be decreased. But if I don't put any data in the delete column that means this user continues using next year.

I have this sample data.

Username	Creation date	Delete	Depart.
as01	12/9/2016		Accountiant
as02	12/9/2016	13/9/2017	Accountiant
as03	12/6/2017		Accountiant
cs01	12/6/2017	13/9/2018	Marketing
CS02	13/6/2017		Marketing

The result from my logic is following below sample chart.

Year	2016	2017	2018
Username	2	5	4

Could you please give me a DAX solution? 

Thank you all for answering.

[MFelix · Accepted solution]

Hi @saranp780,

You need to have in your calculation a reference to the time frame and not only to the start time of the license.

Assuming that your calendar table is not related with your cumulative table.

Use the following measure:

Cumulative License =
VAR Selected_year =
    YEAR ( MAX ( 'Calendar'[Date] ) )
RETURN
    CALCULATE (
        COUNTA ( 'Cumulative'[Username] );
        FILTER (
            ALL ( CUMULATIVE[Creation date ] );
            YEAR ( CUMULATIVE[Creation date ] ) <= Selected_year
        );
        FILTER (
            ALL ( CUMULATIVE[Delete] );
            YEAR ( CUMULATIVE[Delete] ) >= Selected_year
                || CUMULATIVE[Delete] = BLANK ()
        )
    )

See result below:

Regards,

MFelix