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 organ...
  • 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.

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: