SumX of 2 Composite Curves

[Palmtop]

Hello All, 

I have 2 different products, Product 1 & Product 2.  For each product, I have plotted the [Sales] and [Expected], in blue and orange respectively.  I have created a new measure that combines the two by taking the [Sales] curve which switches to [Expected] when it begins.  

MEASURE = 

var _tbl =
ALLSELECTED ( Expected )
var cutoff =
CALCULATE ( MINX ( _tbl, [Date] ) )

return 

CALCULATE (
    SUM ( Sales[Sales] ),
    FILTER ( _calendar, _calendar[Date] < cutoff )
)
    + SUM ( Expected[Sales] )
    

When I select both products however, they do not sum properly.  I believe there is an issue with either my variable 'cutoff' (MINX), or '_tbl' (ALLSELECTED), but I have had no luck fixing it.  Could I please get some advice?

Product 1: 

Product 2: 

Both Selected:

See the dip between the start of the 1st and 2nd [Expected] curves. 

I have included the sample file below.  Please let me know if you have issues accessing it.  

Sample File

Note: I would like for the measure to apply to each product first, then sum the result.  Not sum the curves then apply the measure.  

Thank you!

[v-jianboli-msft]

Hi @Palmtop ,

Please try:

Measure2 =
VAR _a =
    SELECTCOLUMNS ( 'Product', "Product", [Product] )
VAR _b =
    ADDCOLUMNS (
        _a,
        "cutoff",
            MINX (
                FILTER ( ALL ( 'Expected' ), [Product] = EARLIER ( [Product] ) ),
                [date]
            ),
        "Sales",
            CALCULATE (
                SUM ( Sales[Sales] ),
                FILTER ( 'Sales', [Product] = EARLIER ( [Product] ) )
            ),
        "Expected",
            CALCULATE (
                SUM ( Expected[Sales] ),
                FILTER ( 'Expected', [Product] = EARLIER ( [Product] ) )
            )
    )
VAR _c =
    ADDCOLUMNS (
        _b,
        "Value", IF ( MAX ( '_calendar'[Date] ) < [cutoff], [Sales], [Expected] )
    )
RETURN
    SUMX ( _c, [Value] )

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AlexisOlson · Accepted solution]

If you define measures [SumSales] = SUM ( Sales[Sales] ) and [ExpectedSales] = SUM ( Expected[Sales] ), then you can write the composite like this:

SUMX (
    'Product',
    IF (
        ISBLANK ( [ExpectedSales] ),
        [SumSales],
        [ExpectedSales]
    )
)

Note: IF.EAGER instead of IF may be slightly more efficient here.

[Palmtop]

Thank you @AlexisOlson , worked like a charm.  I had tried a sumx before trying the posted measure but had not thought to include the IF ( ISBLANK ( ) ) condition.  

@v-jianboli-msft Your solution also worked for me as well, thank you.