Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Karlie69
Regular Visitor

Difference between values in the same column for unique index

Hi 🙂

I am having trouble calculating the difference between values in the same column.

 

My dataset is like this:

avr.PNG

I added a matrix table based on this data to the dasboard that looks like this:

 

fff.PNG

 

Now I would like to add a column to this table that would calculate the Avr. currency price difference between 2021 and 2020 for each unique Part_no. There are blanks in the data. Unfortunately, I do not know how to write the DAX formula

 

Anyone know the answer? 🙂

1 ACCEPTED SOLUTION
v-eqin-msft
Community Support
Community Support

Hi @Karlie69 ,

 

difff = 
var _pre=CALCULATE(SUM('Table'[Avr]),FILTER('Table',[Part_no]=EARLIER('Table'[Part_no]) && [Year]=EARLIER([Year])-1))
return  IF(_pre=BLANK(),BLANK(), [Avr]-_pre)

Eyelyn9_0-1637894729897.png

Eyelyn9_1-1637894741453.png

 

 

If it's not your expected output, please share more detail information to help us clarify your scenario.

 

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

View solution in original post

6 REPLIES 6
v-eqin-msft
Community Support
Community Support

Hi @Karlie69 ,

 

difff = 
var _pre=CALCULATE(SUM('Table'[Avr]),FILTER('Table',[Part_no]=EARLIER('Table'[Part_no]) && [Year]=EARLIER([Year])-1))
return  IF(_pre=BLANK(),BLANK(), [Avr]-_pre)

Eyelyn9_0-1637894729897.png

Eyelyn9_1-1637894741453.png

 

 

If it's not your expected output, please share more detail information to help us clarify your scenario.

 

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

Thank you very much! It works 🙂 I just changed the formula from SUM to AVERAGE, because in some places I had bad results, but now it's good everywhere.

v-eqin-msft
Community Support
Community Support

Hi @Karlie69 ,

 

According to your dataset screenshots, the Avr is in the same year of each Part_no, so how to calcualte the difference ?

Eyelyn9_0-1637820583307.png

 

Best Regards,
Eyelyn Qin

@v-eqin-msft 

Sorry, this is my mistake. For the purposes of this post, I created an example dataset. The fourth line should be 2021.

 

I would just like to add a calculated column like in Excel pivot tables that will calculate the difference for me between 2021 and 2020. But is it better to make a pivot table in Power Query and calculate the difference there?

I did not think about it before - even if I create this column with DAX, it will not appear next to 2020 and 2021 in the table, but under these years.

amitchandak
Super User
Super User

@Karlie69 , Try new column like

 

New column =
var _max = maxx(filter(Table, [part number] = earlier([part number]) && [year] = earlier([year]) -1), [Avr. Currency Price])
return
[Avr. Currency Price] -_max

Hi @amitchandak ,

 

thanks for your answer. Unfortunately, it doesn't work and I don't know why.

I created the formula like this:

Capture789.PNG

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.