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

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

 

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

    By the way...you HAVE to add a date table!  Time intelligence doesn't work without it.  That's one of your biggest issues...

     

    Download what I did here.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Oh yes! the MTM change worked for me. It is all looking much better now. Can you share the dax for auto calendar creation? - ill add it to mine individually. Also, when it comes to geography and NACI, there are totals included within the data, for example, retail trade within NACI adds up al the all categories and 'Canada' adds all the individual province data; is there a way to integrate that or do i delete those summarizing categories?