Forum Discussion

lpalacios's avatar
lpalacios
Frequent Visitor
8 years ago
Solved

Difference Between two selected year

Hello Everbody:

 

I hope you are well. 

 

I have some experience in PowerBi ( not that advance) and I don´t figure out how to solve this issue.

 

 

I have a table of Purchase by year and organization taken from SAP (picture 1), then I would like to create one column (or measure) that shows the difference between the amounts in usd for 2 selected years. For example: if i selected 2017 and 2018 (picture 2).

 

It will always works to make the difference of 2 columns.

 

do you have any idea on how I can solve it out?

 

kind regards


 

 

 

  • Anonymous's avatar
    Anonymous
    8 years ago

    Hi,

     

    Firstly unpivod your puchase data for multiple year column into single column, lik

     Unpivot your data in edit query

    Then create Three different columns as

    Oldest Year Purchase = CALCULATE(sum(Sheet1[Purchase]), Filter(Sheet1,Sheet1[Years]=Min(Sheet1[Years])))

    Newest Year Purchase = CALCULATE(sum(Sheet1[Purchase]), Filter(Sheet1,Sheet1[Years]=Max(Sheet1[Years])))

    Purchase Difference = [Newest Year Purchase]-[Oldest Year Purchase]

     

    Then apply a slicer to select two table. The Purchase Difference field will always provide you the difference of two selected years.

    Use slicer to select two years

    Please let me know if you face any challange.

3 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    8 years ago

    Hi lpalacios,

    I have a table of Purchase by year and organization taken from SAP (picture 1), then I would like to create one column (or measure) that shows the difference between the amounts in usd for 2 selected years. For example: if i selected 2017 and 2018 (picture 2).

    If I understand you correctly, you should be able to use the formula below to create a new measure to calculate the difference between the two selected year.

    Diff of Selected Years =
CALCULATE (
    [Total Purchase],
    FILTER ( 'Date', 'Date'[Year] = MAX ( 'Date'[Year] ) )
)
    - CALCULATE (
        [Total Purchase],
        FILTER ( 'Date', 'Date'[Year] = MIN ( 'Date'[Year] ) )
    )

    As there isn't an easy way to add the new created measure into the exiting Matrix visual currently, you may need to show the measure with [Org] column on a new Matrix visual. :smileyhappy:

     

    Regards

    • lpalacios's avatar
      lpalacios
      Frequent Visitor
      8 years ago

      thanks!! but it does not work :mansad:

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi,

     

    Firstly unpivod your puchase data for multiple year column into single column, lik

     Unpivot your data in edit query

    Then create Three different columns as

    Oldest Year Purchase = CALCULATE(sum(Sheet1[Purchase]), Filter(Sheet1,Sheet1[Years]=Min(Sheet1[Years])))

    Newest Year Purchase = CALCULATE(sum(Sheet1[Purchase]), Filter(Sheet1,Sheet1[Years]=Max(Sheet1[Years])))

    Purchase Difference = [Newest Year Purchase]-[Oldest Year Purchase]

     

    Then apply a slicer to select two table. The Purchase Difference field will always provide you the difference of two selected years.

    Use slicer to select two years

    Please let me know if you face any challange.