Forum Discussion

HEW's avatar
HEW
Helper III
7 years ago

Difference between 2 columns in a Matrix Table

Hi.

 

I am trying to calculate the difference between 2 columns i Desktop.

The value is a measure and the  columns are one field, pls. see below.

I have tried to create a measure but with no luck. 

For January the new column should be -15

February -12

......

July +38

 

Any suggestions?

Thanks a lot.

Need Help

10 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi HEW,

    Why cant you show the Year on Year measure next to 2018 column in the table?

     

    Regards

    Lokesh

    • HEW's avatar
      HEW
      Helper III
      7 years ago

      I'm not sure I know what you mean.... pls. elaborate :smileyhappy:

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi HEW,

        i mean to say that why cant you use the Year over Year Change measure in the table next to 2018?

         

    • HEW's avatar
      HEW
      Helper III
      7 years ago

      Hi Frank.

       

      Is it really so complicated? I'll try and adapt the formula to my data and hope it works :smileyhappy: Thanks.

       

      Br. Helen

      • v-frfei-msft's avatar
        v-frfei-msft
        Community Support
        7 years ago

        Hi HEW,

         

        I made one sample for your reference.

         

        1. Enter the data as your description and create a calculated table.

         

        Table = SUMMARIZE(Table1,Table1[Month],Table1[mo],Table1[Year],"sum",SUM(Table1[Amount]))

        2. Create a calculated column in Table.

        diff = var prevalue = CALCULATE(FIRSTNONBLANK('Table'[sum],1),
FILTER('Table',('Table'[Year]= (EARLIER('Table'[Year])-1)) && 'Table'[mo]= EARLIER('Table'[mo])))
return
IF(ISBLANK(prevalue), BLANK(),'Table'[sum]-prevalue)

        3. Then we can get the result as we excepted.

         

        For more details, please check the pbix as attached.

         

        Regards,

        Frank

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    Hi,

     

    If there is also a Date column available in your dataset, then you should create a relatiosnhip between this Date column and the Date column of your Calendar Table.  In the Calendar Table, write the following calculated column formulas to extract the Year and Month

     

    Year=year(Calendar[Date])

    Month=FORMAT(Calendar[Date],"mmmm")

     

    In your visual, drag Year and Month from the Calendar Table.  Then write this measure

     

    [Proposals Count]-CALCULATE([Proposals Count],SAMEPERIODLASTYEAR(Calendar[Date]))

     

    Hope this helps.

     

    • HEW's avatar
      HEW
      Helper III
      7 years ago

      Hi Ashish.

       

      It works perfectly! Would it be possible to leave out the difference for 2017 as it is the same value as the count?

       

      Br. Hele