Forum Discussion

lrhoffer's avatar
lrhoffer
Regular Visitor
9 years ago

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

 

5 Replies

  • Eric_Zhang's avatar
    Eric_Zhang
    Microsoft Employee
    9 years ago

    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's avatar
      lrhoffer
      Regular Visitor
      9 years ago

      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's avatar
        Eric_Zhang
        Microsoft Employee
        9 years ago

        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.