Forum Discussion
Helper IIIProduct 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
Community Championxl0911
Please check the latest version of the code and the sample file as I amended as per your reply to speedramps
Helper IIII checked your file and it's not the result I wanted.
I attached a screenshot of the result I want.
 
- tamerj1
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..