Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Counting events

Dear experts, 

 

I'm working on a dataset (directquery) where I have information about memberships. 
e.g.

membership IDStart dateEnd Date
784531201.01.202124.02.2021
685412828.01.2021 
456312031.02.202128.05.2021

 

I'm using this formula to see how many new memberships we have: 

Antall innmeldte =
CALCULATE (
COUNT ( Filteredacan_medlemskap[acan_medlemskapid] ),
USERELATIONSHIP ( 'Date'[Date], Filteredacan_medlemskap[Start Date] )
)
 
This formula to count how many have ended their membership.
Antall utmeldte =
CALCULATE (
COUNTA(Filteredacan_medlemskap[acan_medlemskapid]),
USERELATIONSHIP ( 'Date'[Date], Filteredacan_medlemskap[End Date] )
 
)
 
And this to count the Net
Nett inn/ut = [Antall innmeldte]-[Antall utmeldte]

 

 
This all works perfectly fine!

But then, i'm trying to count how many members we have at a given time. I've tried a lot of formulas and this formula is what gives me the closest result. 
Active members =
VAR selectedDate = MIN('Date'[Date])

RETURN

SUMX('Filteredacan_medlemskap',
VAR employeeStartDate = [Start Date]
VAR employeeEndDate = [End Date]
RETURN IF(employeeStartDate<= selectedDate && OR(employeeEndDate>=selectedDate, employeeEndDate=BLANK() ),1,0))
 
I was thinking, lets say if I know how many members I had 01.01.2021, I can set that number for that date and then use the Net measure to get the exact development per month(or days).  Something like this (but with correct numbers)


I just have NO CLUE on how to do that or if its possible at all. I haven't seen this kind of a solution before.  What do you guys think? 
 
 
 

2 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi, 

       

      Thanks for your tip. I've tried this, and I get the counting but its not correct "enough". 

      Are there no way to do e.g.
      10 000 members 01.01.2021, and then use the net count to calculate the memberships going forward?