Forum Discussion

maart21's avatar
maart21
Regular Visitor
1 year ago
Solved

The difference between scenarios containing different data

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 schedul...
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

  • Anonymous's avatar
    Anonymous
    1 year ago

    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's avatar
lbendlin
Icon for Super User rankSuper User
1 year ago

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's avatar
    maart21
    Regular Visitor
    1 year ago

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