Forum Discussion

xl0911's avatar
xl0911
Icon for Helper III rankHelper III
4 years ago
Solved

Product table and subscription table

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 Su...
Need Help
Tutorial Requests
  • speedramps's avatar
    speedramps
    4 years ago

    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's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

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