Product table and subscription table

[xl0911]

expected result

Hello,

I have a product table that looks like that:

Product Id	Product Name
1	Product Y
2	Product X
3	Product Z

And I have a Subscription Product Status table that a product can be in Status "Active" or "Not Active".

Table Id (Unique)	Product Id	Status	Status Date
1	1	Active	20/01/2018
2	1	Not Active	23/01/2018
3	2	Active	28/01/2018
4	3	Active	14/05/2018
5	1	Active	06/03/2019
6	1	Not Active	12/08/2019
7	3	Not Active	24/02/2020
8	1	Active	06/06/2020
9	2	Not Active	22/12/2021
10	3	Active	02/03/2022

As you can see this Subscription Status Table holds the historical status of every product, and every row represents a status change.

My goal is eventually to create a visual that shows Every month the number of Active products that I had.
I need a formula to create a measure to achieve this goal.

 
 Thank you

[speedramps · Accepted solution]

I have updated the solution as requested to only check if the product was still active at the end of the month. (Originally you implied you wanted active anytime of the month) 

Click here to download a solution 

Now please click thumbs up and accept as solution.
It is not fair to keep changing to problem description.
If we the fix the problem then please accept the solution and raise a new ticket if you want to change the problem description.

WasActive =

// This measure returns a TRUE if the product was still active at the begining of the period

VAR mindate = MIN('Calendar'[Date])

VAR maxdate = MAX('Calendar'[Date])

VAR previousid =

CALCULATE(

    MAX(Facts[Table Id]),

    ALL(Facts[Status Date]),

    Facts[Status Date] < mindate)

VAR previousstatus  =

CALCULATE(

    SELECTEDVALUE(Facts[Status]),

    ALL(Facts),

    Facts[Table Id] = previousid)

RETURN

IF(previousstatus = "Active", TRUE())

IsActive =

// This measure returns

// a TRUE if the product was set active at the end of the period

// a FALSE if the product was set not active at the end of the period

// the previous months valued if it was not set this month

VAR mindate = MIN('Calendar'[Date])

VAR maxdate = MAX('Calendar'[Date])

VAR lastid =

CALCULATE(

    MAX(Facts[Table Id]),

    ALL(Facts[Status Date]),

    Facts[Status Date] >= mindate && Facts[Status Date] <= maxdate)

VAR laststatus  =

CALCULATE(

    SELECTEDVALUE(Facts[Status]),

    ALL(Facts),

    Facts[Table Id] = lastid)

RETURN

SWITCH(TRUE(),

laststatus = "Active", TRUE(),

laststatus = "Not Active", FALSE(),

[WasActive]

)

ActiveProducts =

// This measure counts the number or products that were active

SUMX(

    VALUES(Products[Product Id]),

    INT([IsActive])

)

[speedramps]

 @xl0911  I have accepted my solution because I proivided  2 solutions that worked but you keep changing the problem description.

In future when you raise a problem, please show your thanks to volunteers who help with a thumb click and accept the solution.  One question per ticket please. If you need to extend your request then please raise a new ticket.

You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you ! 

[tamerj1]

Hi @xl0911 
This version is working perfectly in the sample file. Please download the sample file and compare with yours. https://we.tl/t-c14Wj8tpax

# 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 )
    )
)

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[xl0911]

I think your solution is not what I meant because Product Id 1 is not active in the end of the month so under product Id 1 in jan-2018 the count should be 0.

[tamerj1]

Hi @xl0911 
Here is the updated solution with active relationship with the date table and fixed the bug noticed by @speedramps 

https://we.tl/t-NlQUK1FT2T

# Active Subscriptions = 
SUMX (
    VALUES ( Products[Product Id] ),
    CALCULATE (
        VAR PeriodStartDate = 
            MIN ( 'Date'[Date] )
        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 TableBeforePeriodStart = 
            FILTER ( Subscription, Subscription[Status Date] < PeriodStartDate )
        VAR PreviousIactctiveTable = 
            FILTER ( TableBeforePeriodStart, Subscription[Status] <> "Active" )
        VAR LastInactivation = 
            COALESCE ( MAXX ( PreviousIactctiveTable, Subscription[Status Date] ), DATE ( 3000, 1, 1 ) )
        VAR Result =
            IF ( 
                PeriodEnadDate >= LastActivation && PeriodStartDate <= LastInactivation,
                1
            )
        RETURN
            Result,
        CROSSFILTER ( 'Date'[Date], Subscription[Status Date], None )
    )
)

I wanted to upload more screenshots but seems I reached my maximum allowable limit of uploaded images!

[xl0911]

Thank you.

I'm getting this error "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

I guess that maybe the formula can't handle a scenario that maybe a product has 2 rows of status in the same date... ? 

[tamerj1]

@xl0911 
Can you please pase a screenshot of the measure code?

[xl0911]

Found my Mistak..  

[tamerj1]

@xl0911 
Please check the latest version of the code and the sample file as I amended as per your reply to @speedramps 

[xl0911]

I checked your file and it's not the result I wanted.

I attached a screenshot of the result I want.

 

[tamerj1]

@xl0911 

Why is that? Jan should be 1 right as ID 2 is active. What exactly is your business logic?

[xl0911]

Product ID 1 at Jan 2018 should be 0 because in the end this month the product wat "Not Active".

Product ID 1 at March 2019 return to be 1 until the product was "Not Active" at August 2019.

Hope it make sense..

[tamerj1]

@xl0911 
That is exactly what the code is doing. But also Product ID2 is active in Jan therefore it is counted this is why you see 1

[xl0911]

This is a screenshot of your attached file, as you can see you have 2 at Jan 2018

 

[tamerj1]

@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 )
    )
)

[xl0911]

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 🙂

[tamerj1]

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

[xl0911]

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

[tamerj1 · Accepted solution]

Hi @xl0911 
I don't understand what is the problem. Here is your sample file with the very same code

https://we.tl/t-dz8qyjAr2T

[xl0911]

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.