Forum Discussion

maart666's avatar
maart666
Helper I
5 years ago
Solved

Subtract values from two rows in matrix

Hello,

 

Every week I get sales forecasts for which I have to compare the values. Sample model here:



 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

PBI file 

 

I am looking for a way to show the changes between individual forecasts in quantitative and percentage terms, like in this Excel:

File 


 





 

 

 

 

 

Thank you in advance for any tips.

 

Marcin

Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi  maart666 ,

     

    Modify your measure as below:

    Difference = 
VAR thisforecastnum =
    MIN ( Arkusz1[Forecast no] )
VAR thisforecast =
    SUM ( Arkusz1[Qty] )
var lastforecastnum=CALCULATE(MAX('Arkusz1'[Forecast no]),FILTER(ALLSELECTED(Arkusz1),'Arkusz1'[Forecast no]<MAX('Arkusz1'[Forecast no])))
VAR lastforecast =
    CALCULATE (
        SUM ( Arkusz1[Qty] ),
        Arkusz1[Forecast no] = lastforecastnum
    )
RETURN
    IF (
        ISBLANK ( lastforecast ),
        BLANK (),
        thisforecast - lastforecast
    )

     

    And you will see:

     

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

5 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    5 years ago

    This measure expression will give you the difference from the current forecast to the previous one.

     

    Difference =
    VAR thisforecastnum =
        MIN ( Arkusz1[Forecast no] )
    VAR thisforecast =
        SUM ( Arkusz1[Qty] )
    VAR lastforecast =
        CALCULATE (
            SUM ( Arkusz1[Qty] ),
            Arkusz1[Forecast no] = thisforecastnum - 1
        )
    RETURN
        IF (
            ISBLANK ( lastforecast ),
            BLANK (),
            thisforecast - lastforecast
        )

     

    To get your % difference, just change the Return part using the same variable (e.g., DIVIDE(thisforecast - lastforecast, lastforecast)

     

    Regards,

    Pat

  • maart666's avatar
    maart666
    Helper I
    5 years ago

    Thank you Pat!

    It works perfectly when I compare data from subsequent forecasts. But if the forecasts are not consecutive e.g. 1 and 3 (I know I should mentioned this) the values are incorrect:

     

     


    Could you help solve this problem?

    Thank you.

    Marcin

    • v-kelly-msft's avatar
      v-kelly-msft
      Community Support
      5 years ago

      Hi  maart666 ,

       

      Modify your measure as below:

      Difference = 
VAR thisforecastnum =
    MIN ( Arkusz1[Forecast no] )
VAR thisforecast =
    SUM ( Arkusz1[Qty] )
var lastforecastnum=CALCULATE(MAX('Arkusz1'[Forecast no]),FILTER(ALLSELECTED(Arkusz1),'Arkusz1'[Forecast no]<MAX('Arkusz1'[Forecast no])))
VAR lastforecast =
    CALCULATE (
        SUM ( Arkusz1[Qty] ),
        Arkusz1[Forecast no] = lastforecastnum
    )
RETURN
    IF (
        ISBLANK ( lastforecast ),
        BLANK (),
        thisforecast - lastforecast
    )

       

      And you will see:

       

      For the related .pbix file,pls see attached.

       

      Best Regards,
      Kelly

      Did I answer your question? Mark my post as a solution!

    • maart666's avatar
      maart666
      Helper I
      5 years ago

      Hi Kelly,

       

      it looks great accept one case, when in lastforecast there was no value (filed was empty), and in thisforecast is value, mesure do not count that diefference... Is it a way to clear that issue?

      Thank you.

       

      Marcin