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.
I'm trying to calculate the change in price from one month to the next (Ex. September Price minus August Price). I have a column that shows: 201708,201709,201710, etc... The column beside it shows the prices corresponding to that date. The third column shows the grade of oil that is being priced.
For the life of me, I cannot figure out a formula to calculate this (without altering the excel sheet). I can't alter the original excel spreadsheet because it is on a shared drive and automatically updating. I have tried making measures, calculated columns, etc. Nothing works. I want it to automatically update and show the spreads for the first year (and update as the months progress).
Some formulas I have tried (and failed with) are listed below:
- Column 6 = calculate(SUM('ACPriceCurves T_1'[Value]),'ACPriceCurves T_1'[Long Name]IN{"TRADER.LLS"}, 'Num to Date'[Date])-CALCULATE(sum('ACPriceCurves T_1'[Value]), 'ACPriceCurves T_1'[Long Name]IN{"TRADER.LLS"}, PREVIOUSMONTH('Num to Date'[Date]))
-Column 4 = dateadd('Num to Date'[Date],1,month)
- Column 5 = calculate(sum('ACPriceCurves T_1'[Value]),('Num to Date'[Column 4]))-CALCULATE(sum('ACPriceCurves T_1'[Value]),('Num to Date'[Date]))
-
Column 4 = CALCULATE(sum('ACPriceCurves T_1'[Value]),'Num to Date'[Date] IN {MIN('Num to Date'[Date].[Date])})-calculate(sum('ACPriceCurves T_1'[Value]),'Num to Date'[Date] IN NEXTMONTH('Num to Date'[Date].[Date]))
You could create a calendar table and use PREVIOUSMONTH. See more details in the attached pbix file.
calendar table
calendar = ADDCOLUMNS(CALENDAR("2017-01-01","2017-12-31"),"Month",DATE(YEAR([Date]),MONTH([Date]),1))
Measures
previous Month Price = IF ( ISBLANK ( SUM ( yourTable[Price] ) ), BLANK (), CALCULATE ( SUM ( yourTable[Price] ), PREVIOUSMONTH ( 'calendar'[Date] ) ) ) difference = SUM ( yourTable[Price] ) - [previous Month Price]
Thank you so much.. I'm still having difficulty changing the "201708" etc into dates. This is because there are some values in the column that have letters. I want to delete those values. I tried an "IFERROR(value(termlabel)), "not defined)) but it says that I cannot do variant types.
Any tips?
In "Query editor", you first add suffix 01 to each "date".
Then convert that column as date.
The last step, right click and remove errors.
I've heard that if you edit information in query editor, it makes automatic updating more difficult. Do you think this might cause problems with the automatic updating aspect?
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 |
---|---|
107 | |
97 | |
75 | |
65 | |
53 |
User | Count |
---|---|
144 | |
103 | |
98 | |
85 | |
64 |