Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Part# | Date | Price |
A | 2022-01-01 | $ 1.25 |
A | 2022-02-01 | $ 1.25 |
A | 2022-03-01 | $ 1.25 |
A | 2022-04-01 | $ 1.75 |
A | 2022-05-01 | $ 1.75 |
B | 2022-01-01 | $ 3.50 |
B | 2022-02-01 | $ 3.50 |
B | 2022-03-01 | $ 3.50 |
B | 2022-04-01 | $ 3.50 |
B | 2022-05-01 | $ 3.50 |
C | 2022-01-01 | $ 2.00 |
C | 2022-02-01 | $ 2.00 |
C | 2022-03-01 | $ 2.00 |
C | 2022-04-01 | $ 2.20 |
C | 2022-05-01 | $ 2.20 |
Solved! Go to Solution.
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:
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
Hi,
You may download my PBI file from here.
I hope this helps.
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:
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
Any help on this one?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |