2 datasets with start / end date that needs to be 'combined'

[Anonymous]

Dear community members, 

I am new here and have a question: 

I need to make a report that visualises the number of people that have a membership at a certain date. I have the following starting point: 

1 dataset that provides me the client_id and the membership_startdate

1 dataset that provides me the client_id and the membership_enddate (if applicable) 

The difficult part for me is that the client_id numbers will not disappear in the 'startdate dataset' after the membership is ended...

Can anybody tell me how can I make a column chart that gives me the number of active memberships during a certain week or month or per month during a whole year.

I hope someone can help me 🙂

Best regards, Sander

[MFelix]

Hi @Anonymous ,

Without any data is difficult to give you the correct answer, buyt believe that the best option is to merge both tables into a single one and then using a calendar table make the calculation of the active members.

Are you abble to make a single table or do you need the information to be into two different tables?

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

[Anonymous]

Hi @MFelix ,

Thanks for your answer and help. I managed to combine the two tables into one table. Via Wetransfer (see link) I have I would like to share a preview of the (dummy) data. 

Wetransfer link to the PBIX 

Is this sample enough for the 'original' question?

Best regards, Sander

[MFelix]

Hi @Anonymous ,

Add a calendar table now add the following measure:

Active =
COUNTROWS (
    FILTER (
        New_dataset,
         (
            New_dataset[Start_date] <= MAX ( 'Calendar'[Date] )
                && New_dataset[End_date] = BLANK ()
        )
            || (
                New_dataset[Start_date] <= MAX ( 'Calendar'[Date] )
                    && New_dataset[End_date] >= MAX ( 'Calendar'[Date] )
            )
    )
)

[Anonymous]

Hi @MFelix,

Thank you for your measure and clear discription, but this wasn't completely where I was looking for... (must be a wrong description my side... sorry!), so I try do descripe it better this time: 

Based on the data sample I hoped to see a chart that says: 

januari - 4 active (Name_Client 1, 2, 5, 6)

februari - 5 active (Name_Client 1, 3, 4, 5, 6)

March (and further) - 3 active (Name_Client 4, 5, 6)

Best regards, Sander

[MFelix · Accepted solution]

Hi @Anonymous ,

The question was just only regarding the persons that ended during the month so making a small change on the end date everything works 

Active = 
COUNTROWS (
    FILTER (
        New_dataset,
         (
            New_dataset[Start_date] <= MAX ( 'Calendar'[Date] )
                && New_dataset[End_date] = BLANK ()
        )
            || (
                New_dataset[Start_date] <= MAX ( 'Calendar'[Date] )
                    New_dataset[End_date] >= MIN ( 'Calendar'[Date] )
            )
    )
)

Has you can see I use the minimum date for the comparision instead of the max.

PBIX file attach.

[Anonymous]

@MFelix Thank you for your help! Just what I needed! Cheers, Sander