Forum Discussion
Product table and subscription table
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 periodVAR 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)RETURNIF(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 monthVAR 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)RETURNSWITCH(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]))Hi xl0911
I don't understand what is the problem. Here is your sample file with the very same code
I attached a file link: https://we.tl/t-2umLpQQ50q
Hi xl0911
I don't understand what is the problem. Here is your sample file with the very same code
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).
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.
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.
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
This just due to a missing "=" sign after the ">" sign in line # 14.
Here is the updated file https://www.dropbox.com/t/168x2xtLG8b4oHYcThank 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 ?
In the "StatusHistory" Table the subscription ID can repeat again and again but in the Subscription table is unique (PK).
