Count the valid items by months

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! 🙂