Forum Discussion
Count the valid items by months
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!
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! 🙂
2 Replies
- JarroVGIT
Resident Rockstar
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! 🙂