Product table and subscription table

Found my issue, a mean relationship with my date table.

Thank you !!!!!

Hi tamerj1,

As you can see in the screenshot because we have 2 status's with the same date the measure counts it, and of course we need to count the second/largest Id (In the Status History Id field - number 9).

I attached link to download this file: https://we.tl/t-jZGBbCS8hW

Hope that makes sense, thank you !

You are right subscription cannot start with Inactive status (also in my real data).

xl0911 
I would also suggest that in case you still have problems, is better to open a new ticket as this one became conjusted with too many replies and already difficult to track the thread order.

xl0911 
This is not the last file. I shared one more solution after that. Here you go https://we.tl/t-17UszDTihZ

# Active Subscriptions = 
SUMX (
    VALUES ( Products[Product Id] ),
    CALCULATE (
        VAR PeriodEnadDate = 
            MAX ( 'Date'[Date] )
        VAR TableBeforePeriodEnd = 
            FILTER ( Subscription, Subscription[Status Date] <= PeriodEnadDate )
        VAR PreviousActiveTable = 
            FILTER ( TableBeforePeriodEnd, Subscription[Status] = "Active" )
        VAR LastActivation = 
            COALESCE ( MAXX ( PreviousActiveTable, Subscription[Status Date] ), DATE ( 3000, 1, 1 ) )
        VAR PreviousIactctiveTable = 
            FILTER ( TableBeforePeriodEnd, Subscription[Status] <> "Active" && Subscription[Status Date] > LastActivation )
        VAR LastInactivation = 
            COALESCE ( MAXX ( PreviousIactctiveTable, Subscription[Status Date] ), DATE ( 3000, 1, 1 ) )
        VAR Result =
            IF ( 
                PeriodEnadDate >= LastActivation && PeriodEnadDate <= LastInactivation,
                1
            )
        RETURN
            COALESCE ( Result, 0 ),
        CROSSFILTER ( 'Date'[Date], Subscription[Status Date], None )
    )
)

Your solution is perfect BUT its not what I need.

Unfortunately I did a big mistake in my simulation and I need to add one more table (you see, any customer can subscribe to any product ID so I miss the sales table between the product table and the status history table).

Now I don't know what to do, should I open a new question or continue from here, what do you suggest ? 

thanks for not giving up 🙂

xl0911 
Thats fine. Please add all relevant information and sample data including the relevant part of your data model and relastionships 

I attached a file link:  https://we.tl/t-2umLpQQ50q

I entered new value's so you can see the mistake, here is the link :https://we.tl/t-1hmf4eHwnk

Check Jan-2018 it suppose to return total 4 but it returns total 2.  

Hi,

You may download my revised PBI file from here.  The answer for Jan 2022 is 4.

Hope this helps.

I found A small problem in your solution.

If there is the same date with the same Product Id more then one time its not "tie breaker" with the largest ID.

xl0911 
If there is a record with same ID and same Date then there should be a value from a third column that is different. Otherwise it is a dupicated record. But in this case what do you mean by largest ID while we're talking about the same ID same Date?

I mean, same Subscription ID (is a foreign key) and same Status Date, so the "tie breaker" is the Staus History ID witch is a primary key on that Status History table.

xl0911 

Yes of course a unique column required. I'm outside office now. I will check once I return 

xl0911 
This just due to a missing "=" sign after the ">" sign in line # 14.
Here is the updated file https://www.dropbox.com/t/168x2xtLG8b4oHYc

Thank you tamerj1, but still it's not working properly, as you can see in the attached screenshot, if in the same date the highest ID is Active, it should be count it (and it's not)

Hi tamerj1, Any new to my last post ?

Hi xl0911 
Sorry I was so busy today I didn't have the chance to look at it. Is it possible for the subsription ID to be repeated again and again? This is a big complication of the issue

xl0911 
The subscription cannot start with Inactive status! Do you have the same scenario in your real data?

In the "StatusHistory" Table the subscription ID can repeat again and again but in the Subscription table is unique (PK).

xl0911 
Ok but it is worth it that you check carefully with your real data and make sure no other issues.