Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

% change from previous time stamp

Hello All!   I am trying to calculate % change in value from a previous time stamp. To put simply, my data is organized by date, category, sub category, sub-sub category and value; hence, there are...
Need Help
  • littlemojopuppy's avatar
    littlemojopuppy
    5 years ago

    Anonymous the formulas work fine as is.  The current problem is that the chart has an axis with individual dates so it presents very spikey.  It calculates a big increase on the 1st of the month because the data in fact table is all as of the first of the month and then an equally large decrease on the 2nd.

     

    Once Anonymous makes the adjustments to the visualizations I suggested they'll have a month to month representation and they'll be fine.

     

Anonymous's avatar
Anonymous
Not applicable
5 years ago

littlemojopuppy  Anonymous 

 

I have recreated the whole thing again and have not used a date table this time and let PBI create its own hierarchy. There is some progress but the information is still inaccurate. where there should be a negative % change, it is still showing as a positive change. There is something off in the formulas:

 

 

Also, these changes should be much higher. The previous month / date function needs to maybe change in some way?

 

The source file and new file are here for reference

https://1drv.ms/u/s!ApVe-eCmnWcTuF-u_w2dlNdjVKAe?e=gwAEG1

 

Thank you for helping guys! 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    HI Anonymous,

    I think this should works as expected, your formula is calculated on month level but the chart displays with quarter level. The big changes on quarter level do not mean it also obviously on month level.

    I drill to month level and it correctly shows the negative values and graphics:

    Regards,

    Xiaoxin Sheng

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hmm, so i get what you are saying. And i think this can probably work but why is the last data point always -1? - is there a way to fix this some how?

      • littlemojopuppy's avatar
        littlemojopuppy
        Icon for Community Champion rankCommunity Champion
        5 years ago

        Hi Anonymous .  The last data point is always a -100% decline because there isn't a value for the last month

        Modify the MTM Change measure like this

        MTM Change = 
    IF(
        ISBLANK([TOTAL]),
        BLANK(),
        [TOTAL]-[Value of Previous Month]
    )

        After doing that, you get this...

        and the chart looks like this