Add change between years as a column to the table

[PaulShlapa]

Dear Experts,

I have data in a following table

What i would like to do is to add another column that would show difference in price for each material between years. It should look like that

Additionally, i would like to create a measure that would calculate difference for each material between years, depending on the selection of the slicer "Year" from the same table.

For example if i choose a period from 2021 to 2024:

A = 4

B = 5

Any help would be appreciated.

Sincerely,

Pavlo

[PaulShlapa]

Many thanks to both of you @amitchandak and @v-yangliu-msft 

[v-yangliu-msft · Accepted solution]

Hi  @PaulShlapa ,

Thank @amitchandak  for answering the questions and sharing your knowledge, I'm going to add to the first question and answer the second one here.

Here are the steps you can follow：

Question1：

Create calculated column.

Difference =
var _test=
SUMX(
    FILTER('Table',[Material]=EARLIER([Material])&&[Year]=EARLIER([Year])-1),[Price])
return
IF(
    _test=BLANK(),BLANK(),
    [Price] - _test)

Question2：

Create measure.

Measure =
var _mindate=MINX(ALLSELECTED('Table'),[Year])
var _maxdate=MAXX(ALLSELECTED('Table'),'Table'[Year])
var _value1=SUMX(FILTER(ALL('Table'),'Table'[Material]=MAX('Table'[Material])&&'Table'[Year]=_mindate),[Price])
var _value2=SUMX(FILTER(ALL('Table'),'Table'[Material]=MAX('Table'[Material])&&'Table'[Year]=_maxdate),[Price])
return
_value2 - _value1

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[amitchandak]

@PaulShlapa , in case you need a new column

Last year = sumx(filter(Table, [Material] = earlier([Material]) && [Year] = earlier([Year]) -1), [Price])

In case you need a measure,

Previous Year = CALCULATE(sum(Table[Price]),OFFSET(-1, ALL('Table'[Year],'Table'[Material]),ORDERBY('Table'[sort]),KEEP,PARTITIONBY('Table'[Material])))

Power BI DAX- Earlier, I should have known Earlier: https://youtu.be/CVW6YwvHHi8

Continue to explore Power BI Offset Compare Categories, Time Intelligence MOM, QOQ, and YOY: https://youtu.be/5YMlkDNGr0U