Count active subscriptions in variable timeframe

[ciken]

Hi PBI community,

I am working hard to count active subscriptions based on the product purchased in a given time frame. 

EXAMPLE: Greg Smith buys Product A and Product B on subscription, I am wanting to count him as 2 active subscriptions...but when I use the current measure with COUNTA, it counts EVERY order within my time frame, but, if I switch it to DISTINCTCOUNT, it will only count the product one time for everyone.  It almost needs to be two different measures but I was trying to see if it could be done in one.  

Here is my current measure - I'd love any suggestions...and if it is to create a separate one based on product vs consumer, let me know that too.

Thanks

Corey

Active AR Subscriptions =

CALCULATE(COUNTA('Line Items'[OrderedProductSKU]),DATESINPERIOD(CalendarTable[Dates], LASTDATE(CalendarTable[Dates]),-'Retention By Day'[Retention By Day Value],DAY))

[v-rzhou-msft]

Hi @ciken ,

According to your code, I find there are three tables 'Line Items'/'CalendarTable'/'Retention By Day'. I think 'Line Items' should be the fact table. 'CalendarTable' and 'Retention By Day' should be dimtables which should have relationships with 'Line Items'. If your relationship looks like as I mentioned, I suggest you to set the direction of these relationships from single to both. Then have a try.

If this reply still couldn't help you solve your issue, please share a sample file with me and show me with a screenshot with the result you want. I need to know your data model. This will make it easier for me to find the solution.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.