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

)

[tamerj1]

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

[xl0911]

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 !

[tamerj1]

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]

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

[tamerj1]

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

[tamerj1]

@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

[xl0911]

Hi tamerj1, Any new to my last post ?

[xl0911]

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)

 

[tamerj1]

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

[xl0911]

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

[tamerj1]

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

[tamerj1]

@xl0911 

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

[xl0911]

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]

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

Thank you !!!!!

[xl0911]

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.  

[Ashish_Mathur]

Hi,

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

Hope this helps.

[speedramps]

Sorry @tamerj1, I think there is a bug in your DAX

Only one product was active in Feb 2018 but your report shows two.

Thanks for trying to help xl0911

Please can you double check my solution (see above).
Two pairs of eyes are better than one.

[tamerj1]

@speedramps 

yes you are right. I will fix once I reach home

[speedramps]

Ha Ha I see that tamerj1 has also posted solution. 

Execllent !

It will be interesting to compare the output from both methods and see if they produce the same results.

[speedramps]

Thank you  xl0911 for a very clear explantion of your problem with example data as tables (not screen shots).
So I could import the data and build this solution. Wish all members gave such clear decsriptions!

Click here to download the solution  

Quote @speedramps when you raise new problems if you can describe them all as nicely as this and I will receive an notification.

I created 3 measures

WasActive =

// This measure returns a TRUE is 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 is the product was set to active at any time duting the period

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

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

VAR activethisperiod =

CALCULATE(

    SELECTEDVALUE(Facts[Status]),

    ALL(Facts[Status Date]),

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

    Facts[Status] = "Active")

RETURN

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

 

ActiveProducts =

// This measure counts the number or products that were either active at the begining of the period or were set to active during the period

SUMX(

    VALUES(Products[Product Id]),

    INT( [IsActive] || [WasActive])

)

 

 

My report shows the active products per month in the top left.

The top right visual is an audit trail to help with testing. 

The bottom righ is your source data.

If you clcic a product in the audit report them the source data will filter accordingly to help with testing. 

 

[xl0911]

Thank you speedramps

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.