Finding Value Change between Months

[lrhoffer]

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]))

[Eric_Zhang]

@lrhoffer

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]

[lrhoffer]

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?

[Eric_Zhang]

@lrhoffer

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.

[lrhoffer]

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?

[Eric_Zhang]

---

@lrhoffer wrote:

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?

---

@lrhoffer

I've not encountered any problem on automatic updating.