The difference between scenarios containing different data

[maart21]

Hi, I really need help as I haven't found an answer despite searching the forums.

I have production forecasts (MPS) for individual products (Product), each row represents one product that is scheduled to be completed in the month specified in the PLAN-FINISH.

I need to build a matrix where, after selecting several scenarios on slicer, the difference between these scenarios will be calculated. If there is no planned production completion for a specific month, it should be treated as 0 so that the difference between the scenarios can be calculated correctly. Just like in the image below:

I am attaching my sample file 
Thank you in advance for any help.

[v-xinc-msft · Accepted solution]

Hi @maart21 ,

Please try the measure:

Measure = 
    Var _Previous=
        CALCULATE(
            SUM(Arkusz1[Qty]),
            FILTER(
                ALL(Arkusz1),
                [MPS]="B"&
                RIGHT(SELECTEDVALUE(Arkusz1[MPS]),2)-1&&
                [PLAN-FINISH].[Miesiąc]=SELECTEDVALUE(Arkusz1[PLAN-FINISH].[Miesiąc])&&
                [Produkt]=SELECTEDVALUE(Arkusz1[Produkt]
        )
    )
)
RETURN
SUM(Arkusz1[Qty])-_Previous

Best regards,

Lucy Chen

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

[v-xinc-msft · Accepted solution]

Hi @maart21 ,

Please try the measure:

Measure = 
    Var _Previous=
        CALCULATE(
            SUM(Arkusz1[Qty]),
            FILTER(
                ALL(Arkusz1),
                [MPS]="B"&
                RIGHT(SELECTEDVALUE(Arkusz1[MPS]),2)-1&&
                [PLAN-FINISH].[Miesiąc]=SELECTEDVALUE(Arkusz1[PLAN-FINISH].[Miesiąc])&&
                [Produkt]=SELECTEDVALUE(Arkusz1[Produkt]
        )
    )
)
RETURN
SUM(Arkusz1[Qty])-_Previous

Best regards,

Lucy Chen

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

[maart21]

Thank you v-xinc-msft, 
looks good, but when I add sums to my my matrix,  values are not correct.

[v-xinc-msft · Accepted solution]

Hi @maart21 ,

Please try this.

Result = 
Var _table=SUMMARIZE(Arkusz1,[MPS],Arkusz1[Produkt],Arkusz1[PLAN-FINISH].[Miesiąc],"Diff",[Measure])
RETURN
SUMX(_table,[Diff])

 

Best regards,

Lucy Chen

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

[lbendlin]

To report on things that are not there you need to use disconnected tables and/or crossjoins

In your case you need to create a product dimension table and a month dimension table.  Then use measures to show the count for each combination, as well as the delta between the MPS versions.

[maart21]

Hi lbendlin,

thank you for tip, it takes a while but I was able to create measure that works.

Difference = 
VAR CurrentValue =
    CALCULATE(
        SUM(Arkusz1[Qty]),
        TREATAS(VALUES(DimMPS[MPS]), Arkusz1[MPS]),
        TREATAS(VALUES(DimMonths[Date]), Arkusz1[PLAN-FINISH]),
        VALUES(Arkusz1[Produkt])
    )
VAR PreviousMPS =
    CALCULATE(
        MAX(DimMPS[MPS]),
        FILTER(
            ALL(DimMPS),
            DimMPS[MPS] < SELECTEDVALUE(DimMPS[MPS])
        )
    )
VAR PreviousValue =
    CALCULATE(
        SUM(Arkusz1[Qty]),
        TREATAS({PreviousMPS}, Arkusz1[MPS]),
        TREATAS(VALUES(DimMonths[Date]), Arkusz1[PLAN-FINISH]),
        VALUES(Arkusz1[Produkt])
    )
RETURN
IF(
    ISBLANK(CurrentValue) && ISBLANK(PreviousValue),
    0,
    COALESCE(CurrentValue, 0) - COALESCE(PreviousValue, 0)
)