topic Re: Calculate historical data from membership register in DAX Commands and Tips

Calculate historical data from membership register

Anonymous — Mon, 26 Sep 2022 13:18:59 GMT

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

Re: Calculate historical data from membership register

DimaMD — Mon, 26 Sep 2022 13:43:28 GMT

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

Re: Calculate historical data from membership register

Anonymous — Mon, 26 Sep 2022 13:50:42 GMT

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.

Re: Calculate historical data from membership register

DimaMD — Mon, 26 Sep 2022 14:08:11 GMT

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

Re: Calculate historical data from membership register

Anonymous — Mon, 26 Sep 2022 14:16:08 GMT

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.

Re: Calculate historical data from membership register

DimaMD — Mon, 26 Sep 2022 14:48:20 GMT

Hi tamerj1 , maybe you have some solution for Anonymous

Re: Calculate historical data from membership register

tamerj1 — Mon, 26 Sep 2022 15:53:33 GMT

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

Re: Calculate historical data from membership register

tamerj1 — Tue, 27 Sep 2022 09:23:04 GMT

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.

Re: Calculate historical data from membership register

Anonymous — Fri, 30 Sep 2022 07:09:08 GMT

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 😀

Re: Calculate historical data from membership register

tamerj1 — Fri, 30 Sep 2022 07:34:27 GMT

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?

Re: Calculate historical data from membership register

Anonymous — Fri, 30 Sep 2022 07:44:05 GMT

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.

Re: Calculate historical data from membership register

tamerj1 — Fri, 30 Sep 2022 11:04:44 GMT

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" ) )

Re: Calculate historical data from membership register

Anonymous — Fri, 30 Sep 2022 11:12:46 GMT

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