Cumulative of Current Members at a given date

[soggy_weetbix]

I have a list of members, I want to make an area chart or even a table, to show how overtime members come and go.

Here is an example list of data to use:

Member_ID	Start	End
ID1	1-Mar-23	30-Jun-23
ID2	1-Apr-23	31-Jul-23
ID3	1-Apr-23	31-Jul-23
ID4	1-Apr-23	31-Jul-23
ID5	1-Apr-23	31-Jul-23
ID6	1-Apr-23	31-Aug-23
ID7	1-May-23	30-Nov-23
ID8	1-Jun-23	30-Nov-23
ID9	1-Aug-23	30-Nov-23

The end result should be something like this, in months but I'd also like to also see one for days:

Jan	0
Feb	0
Mar	1
April	6
May	7
Jun	8
Jul	7
Aug	4
Sep	3
Oct	3
Nov	3
Dec	0

I've spent hours trying to work this out using DAX and new columns, but can't seem to figure out what I am doing wrong.

Any help would be greatly appreciated.

Thanks

[ThxAlot]

[soggy_weetbix]

Thanks

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

expected result measure: = 
COUNTROWS (
    FILTER (
        'Member',
        'Member'[End] >= MIN ( 'Calendar'[Date] )
            && 'Member'[Start] <= MAX ( 'Calendar'[Date] )
    )
) + 0

[soggy_weetbix]

Thanks - this one works best!

I think my problem was I was creating a relationship with start date, but it wasn't needed.

Thanks so much 🙂