Forum Discussion
Finding Value Change between Months
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 th...
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?