End of month backlog

[Chetanab]

Please help me with the below -

I have a history table like below. I want a distinct count of IDs with status=Active. If an ID was active earlier and resolved in a particular month, it should not be counted in the backlog for that month end.

Work Item Id	CreateDate	ChangeDate	Status	Eomonth
1	1/3/2022	1/12/2022	Active	1/31/2022
2	1/5/2022	1/13/2022	Active	1/31/2022
2	1/5/2022	2/2/2022	Resolved	2/28/2022
3	1/5/2022	1/5/2022	Active	1/31/2022
3	1/5/2022	2/20/2022	Resolved	2/28/2022
4	1/5/2022	1/5/2022	Active	1/31/2022
4	1/5/2022	2/3/2022	Active	2/28/2022
4	1/5/2022	3/3/2022	Resolved	3/31/2022

expected result

Eomonth	Backlog
1/31/2022	4
2/28/2022	2
3/31/2022	1

Notes - 

As of 1/31/2022 - all 4 IDs were active

as of 2/28/2022 - ID1 & ID4 were active

as of 3/31/2022 - only ID1 was active

[Chetanab]

The above solution does not work if a particular ID has no change date in the month it was created. for example in thhe below table, ID 6 was created in Jan 2022 but first changed in Feb 2022. It should still be counted in Jan backlog because it was active in Jan. 

Work Item Id	CreateDate	ChangeDate	Eomonth	Status
1	3-Jan-22	12-Jan-22	31-Jan-22	Active
2	5-Jan-22	13-Jan-22	31-Jan-22	Active
2	5-Jan-22	2-Feb-22	28-Feb-22	Resolved
3	5-Jan-22	5-Jan-22	31-Jan-22	Active
3	5-Jan-22	20-Feb-22	28-Feb-22	Resolved
4	5-Jan-22	5-Jan-22	31-Jan-22	Active
4	5-Jan-22	3-Feb-22	28-Feb-22	Active
4	5-Jan-22	3-Mar-22	31-Mar-22	Resolved
5	5-Jan-22	5-Jan-22	31-Jan-22	Active
5	5-Jan-22	3-Feb-22	28-Feb-22	Resolved
5	5-Jan-22	3-Mar-22	31-Mar-22	Resolved
6	5-Jan-22	3-Feb-22	28-Feb-22	Active
6	5-Jan-22	3-Mar-22	31-Mar-22	Resolved

[SpartaBI · Accepted solution]

I see what you mean. V1 whould have work if it would get a corresponding value of EOM when it is opened and not change (This case wasn't shown in the orginal data you shared). So let's do V2:

Backlog =
VAR _current_eom =
    MAX ( 'Table'[Eomonth] )
VAR _all =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Work Item Id] ),
        'Table'[CreateDate] <= _current_eom,
        REMOVEFILTERS('Table')
    )
VAR _resolved =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Work Item Id] ),
        'Table'[Eomonth] <= _current_eom,
        'Table'[Status] = "Resolved"
    )
RETURN
    _all - _resolved

[Chetanab]

Thank you 🙂 🙂

[Chetanab]

Thanks for getting back @SpartaBI . The expected result is 

where as the above DAX gives

Please let me know if there is a way to achieve the expected result. Maybe I need a date table? I am also thinking to create another table with create date endofmonth and merging it with this table to use v1

[SpartaBI]

Could you please add:

REMOVEFILTERS('Table') to the first CALCULATE

[SpartaBI]

Hey @Chetanab ,

Put Eomonth on the rows of a matrix and this measure in the values

Backlog =
VAR _current_eom =
    MAX ( 'Table'[Eomonth] )
VAR _all =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Work Item Id] ),
        'Table'[Eomonth] <= _current_eom
    )
VAR _resolved =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Work Item Id] ),
        'Table'[Eomonth] <= _current_eom,
        'Table'[Status] = "Resolved"
    )
RETURN
    _all - _resolved

[Chetanab]

Awesome. Thank you @SpartaBI