Calculate historical data from membership register

[Anonymous]

Hi all,

I have a dataset which contains a lot of information about our members.
I would like to be able to calculate how many members we had on different dates. For example, how many active members where there in January 2019, 2020, and so on.

I have created a dummy-file to make it easier to understand the data.
This is the link to the dummy-file: https://www.dropbox.com/s/hy42wzs7prqcw9y/Dummy-file%20members.pbix?dl=0

It would be great if there was an ability to just use a date filter/slicer and the exact number on the date you choose would come up.

Best regard

Andreas

[tamerj1 · Accepted solution]

Hi @Anonymous 
not sure if this is what you're looking for

Count = 
VAR CurrentDate = MAX ( 'Date'[Date] )
RETURN
    COUNTROWS ( 
        FILTER ( 
            Tabell2,
            Tabell2[From_date] <= CurrentDate 
                && Tabell2[To_date] >= CurrentDate
                && Tabell2[Status] = "Active"
        )
    )

[Anonymous]

That's exactly what I want, thank you legend @tamerj1 ! 😀

[tamerj1]

Hi @Anonymous 
Please clarify your logic when selecting a period rather than selecting a single date. For example if the maximum selected date is after the [To_Date] shall the account be counted as Active or Ended? Same for the minimum selected date compared to [From_Date]. Please consider all cases and advise.

[Anonymous]

Hi @tamerj1,
Thank you for your reply!

The " To_Date" updates automatically everyday and shows todays date if the member still has an active account. If the member decided to end its membership yesterday, the "To_date" would not update anymore and be set to 30.09.2022 and the status would change to "Ended"

The "From_date" only gives us information about when the member joined and it will not change.

Status	Membership	From_date	To_date
Active	Gold	01.01.2022	30.09.2022
Ended	Silver	01.01.2022	01.06.2022

This table simplify the problem very much. 

As of today we have one active member, but i want to be able to choose a date this year, for example 01.05.2022 and be able to see that at this time we had 2 active members. Even though the second member in the table does not have an "active" status as of today, he still had it 01.05.2022.

Hopefully this was clarifying 😀

[tamerj1]

@Anonymous 

Thank you for the clear description. However this was not my question. You are talking about selecting a single date, but what about selecting a period, a week of a month or just random period in the time line slicer?

[Anonymous]

Sorry i might have misunderstood your question @tamerj1  😀

The logic behind it is becuase i want to be able to compare how many active members there were for example in 01.05.2021 vs 01.05.2022. I have not found a sloution to go back in time and see how many "activ" members there were on a specific date..

Another usefule reason to do this could be to see the growth, if i can se how many active members we had 01.01.2022 and compare it to how many there are today i could calculate the growth.

[DimaMD]

Hi @tamerj1 , maybe you have some solution for @Anonymous 

[tamerj1]

Extremely busy these days. I'll try to have a look at it tomorrow

[DimaMD]

Hi @Anonymous Please review the file to see if I understood your problem correctly

[Anonymous]

Hi @DimaMD 

Thank you for your reply 😊
I think we maybe have misunderstood eachother. 

If you see the picture below i want to be able to just drag the dates to for example 29.11.2020 as show in the picture at the number of active mebers on that day will show.

[DimaMD]

@Anonymous In the example you provided, there is no such date, do you want to see in the period "From_date" and "To_date"

[Anonymous]

@DimaMD Sorry if i have been a bit unclear 😊

The "from_date" shows when the person joined as a member and the "To_date" shows information about when the perons ended his/her membership. If the person still is a active member the "To_date" will be sat as the date today.

So i want to be able to go back in time and see how many activ members where there on specific dates.