Product table and subscription table

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.

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

Hi xl0911 
Here is the a sample file with the solution https://we.tl/t-RX6Wox2KRZ

# Active Subscriptions = 
SUMX (
    VALUES ( Products[Product Id] ),
    CALCULATE (
        VAR CurrentDate = 
            MAX ( 'Date'[Date] )
        VAR PreviousTable = 
            FILTER ( Subscription, Subscription[Status Date] <= CurrentDate )
        VAR PreviousActiveTable = 
            FILTER ( PreviousTable, Subscription[Status] = "Active" )
        VAR LastActivation = 
            MAXX ( PreviousActiveTable, Subscription[Status Date] )
        VAR PreviousIactctiveTable = 
            FILTER ( PreviousTable, Subscription[Status] <> "Active" && Subscription[Status Date] > LastActivation )
        VAR LastInactivation = 
            COALESCE ( MAXX ( PreviousIactctiveTable, Subscription[Status Date] ), DATE ( 3000, 1, 1 ) )
        RETURN
            IF ( 
                CurrentDate <= LastInactivation && CurrentDate >= LastActivation,
                1
            )
    )
)

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. 

 

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.

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!

Hi,

You may download my PBI file from here.

Hope this helps.

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