Count the valid items by months

[Anonymous]

Dear Community,

I am building a data dashboard for a fitness center and I need some help with calculating the valid number of memberships at the end of every month.
I have the dates, when I sold the different yearly or monhly memberships and the dates until these memberships are valid.
How can I calculate how many valid memberships do I have at the end of every month? Thank you in advance!

file 

[Anonymous]

Thank yor very much for the great solution!

Agnes

[JarroVGIT · Accepted solution]

So, on first glance you need to decide when we count a valid subsciption within a month and when not. There is a valid subscription on 02/06/2019 yet your table shows 0 for 06.2019. What is the border? What if the enddate is 17/06/2019? Does the value change to 1? What if it is 29/06/2019? Still 0 or actually 1? You need to define that boundry.

I loaded your data into PowerBI and created a calculated table that has every day for each valid membership in it. Because of the question above and your expected result, I assumed that we won't count the month in which a membership ends. The calculated table is this:

GeneratedTable = 
GENERATE('Table',    
    VAR _curStartDate = 'Table'[date sold]
    VAR _curEndDate = EOMONTH('Table'[valid until], -1)
    RETURN
    GENERATESERIES(_curStartDate, _curEndDate))

Then I create a measure that counts the distinct memberships:

DistinctMembers = DISTINCTCOUNT(GeneratedTable[Member number])

Then I created a table visual with DateSold (only Year/Month) and the measure:

PBIX file can be found here: https://1drv.ms/u/s!Ancq8HFZYL_aiItDUonqIklYNkn7Nw?e=SZ73Cx 

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂