topic Re: The difference between scenarios containing different data in DAX Commands and Tips

The difference between scenarios containing different data

maart21 — Fri, 06 Dec 2024 13:11:13 GMT

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.

Re: The difference between scenarios containing different data

lbendlin — Sat, 07 Dec 2024 01:03:41 GMT

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.

Re: The difference between scenarios containing different data

Anonymous — Mon, 09 Dec 2024 09:24:34 GMT

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.

Re: The difference between scenarios containing different data

maart21 — Mon, 09 Dec 2024 10:28:24 GMT

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

Re: The difference between scenarios containing different data

maart21 — Mon, 09 Dec 2024 13:04:01 GMT

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

Re: The difference between scenarios containing different data

Anonymous — Fri, 13 Dec 2024 08:18:38 GMT

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.