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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
powerjey
Helper I
Helper I

How to dynamically calculate the cost difference between two dates per item?

Hi everyone,

 

Looking for some help to calculate the cost increase/decrease between two date points per product. I would like to display any increases/decreases per item. Each item in the list are part of a BOM for an overall product. An example scenaro is needing to show any cost differences between 2022-03-01 & 2022-04-01 which would show changes in A ($0.50) and C ($0.20).

 

I'm guessing I would need to utilize the MAX/MIN functions in a measure, but having trouble creating a measure that compares each item. There are too many date points to due the calculation per date via a new column and all the data is unpivoted. 

 

  • 2 date values are selected via the standard PBI slicer visual
  • The underlying data format in PBI is displayed below:  
Part#Date Price 
A2022-01-01 $                                          1.25
A2022-02-01 $                                          1.25
A2022-03-01 $                                          1.25
A2022-04-01 $                                          1.75
A2022-05-01 $                                          1.75
B2022-01-01 $                                          3.50
B2022-02-01 $                                          3.50
B2022-03-01 $                                          3.50
B2022-04-01 $                                          3.50
B2022-05-01 $                                          3.50
C2022-01-01 $                                          2.00
C2022-02-01 $                                          2.00
C2022-03-01 $                                          2.00
C2022-04-01 $                                          2.20
C2022-05-01 $                                          2.20
1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

Hi  @powerjey ,

Here are the steps you can follow:

1. Create measure.

Measure =
var _min=MINX(ALLSELECTED('Table'),[Date])
var _max=MAXX(ALLSELECTED('Table'),[Date])
return
SUMX(FILTER(ALL('Table'),'Table'[Date]=_max&&'Table'[Part#]=MAX('Table'[Part#])),[Price])
-
SUMX(FILTER(ALL('Table'),'Table'[Date]=_min&&'Table'[Part#]=MAX('Table'[Part#])),[Price])

2. Result:

vyangliumsft_0-1661736930400.png

If you need pbix, please click here.

How to dynamically calculate the cost difference between two dates per item.pbix

 

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

View solution in original post

5 REPLIES 5
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

I hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-yangliu-msft
Community Support
Community Support

Hi  @powerjey ,

Here are the steps you can follow:

1. Create measure.

Measure =
var _min=MINX(ALLSELECTED('Table'),[Date])
var _max=MAXX(ALLSELECTED('Table'),[Date])
return
SUMX(FILTER(ALL('Table'),'Table'[Date]=_max&&'Table'[Part#]=MAX('Table'[Part#])),[Price])
-
SUMX(FILTER(ALL('Table'),'Table'[Date]=_min&&'Table'[Part#]=MAX('Table'[Part#])),[Price])

2. Result:

vyangliumsft_0-1661736930400.png

If you need pbix, please click here.

How to dynamically calculate the cost difference between two dates per item.pbix

 

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

Thanks so much! Works perfectly. 

I've learned alot from this post. 

In my case I have used your guidance to fix my issue.. Thanks a lot

 

% new perc =
var w_previous = MINX(ALLSELECTED('DP Fcast (2)'),[Week_final])
var w_current = MAXX(ALLSELECTED('DP Fcast (2)'),[Week_final])

var diff1 = SUMX(FILTER(ALL('DP Fcast (2)'),'DP Fcast (2)'[Week_final]= w_current&&'DP Fcast (2)'[Location]=MAX('DP Fcast (2)'[Location])),[Fcast Volume(in EA) by Location])
var diff2 = SUMX(FILTER(ALL('DP Fcast (2)'),'DP Fcast (2)'[Week_final]= w_previous&&'DP Fcast (2)'[Location]=MAX('DP Fcast (2)'[Location])),[Fcast Volume(in EA) by Location])

var diff = diff1 - diff2
var total = (diff1 + diff2) / 2
var diff_perc = diff / total
var result = diff_perc
return resultPowerbiScreenshot 2022-09-27 092624.jpg
powerjey
Helper I
Helper I

Any help on this one?

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.