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 duplicate dates. I wanted charts to calculate the % change from the previous date regardless of what category or sub or sub-sub category is selected. Can anyone help? Picture of data and link to sample dashboard as follows: Sample Dash

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.

% change from previous time stamp | Microsoft Fabric Community

27 Replies

Anonymous
Not applicable
5 years ago
Thanks littlemojopuppy and Anonymous ; i am not sure what i finally did to make it all look right but was finally able to do it. The final dashboard is here: https://app.powerbi.com/view?r=eyJrIjoiNjQ5OGRjOGItNTNmMS00YjViLThlZjctMzcwNGMyMGI4MTFjIiwidCI6Ijc1ZGYwOTZjLThiNzItNDhlNC05YjkxLWNiZjc5ZDg3ZWUzYSIsImMiOjl9&embedImagePlaceholder=true

Happy new year guys!
littlemojopuppy
Community Champion
5 years ago
Can you provide sample data to work with (not in a picture)?
amitchandak
Super User
5 years ago
Anonymous , new column, when you only need date not other =

As a new column =
As a new column =
new column =
var _max = maxx(filter(Table, [REF_Date] < earlier([REF_Date])),[REF_Date])
return
divide([Value] - maxx( filter(Table, [REF_Date] =_max),[value]),maxx( filter(Table, [REF_Date] =_max),[value]))

or add few like

new column =
var _max = maxx(filter(Table, [REF_Date] < earlier([REF_Date]) && [GEO] = earlier([GEO]) ),[REF_Date])
return
divide([Value] - maxx( filter(Table, [REF_Date] =_max && [GEO] = earlier([GEO]) ),[value]),maxx( filter(Table, [REF_Date] =_max),[value]))

refer this blog check option as measure too

https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c3243d1f9
- Anonymous
  Not applicable
  5 years ago
  Time intelligence was not working. I am unable to put your solution through. The data file and the PBI file are here:
  https://1drv.ms/u/s!ApVe-eCmnWcTuFogP__6IMAxLc8v?e=DGgVJI
  
  Is it possible to give it a quick look please?
littlemojopuppy
Community Champion
5 years ago
Hi Anonymous

You've got a couple things going on here...

First you need to mark your date table as a date table (right click on it on the furthest right hand pane and you should find it).

Second you're using columns for everything. Generally speaking, using calculated columns should be avoided. In almost all cases, using measures is preferable because they are more dynamic. That said, add these measures to your data model:
Total Actual Value = SUM('Canada Retail Trade - PRIME'[ActualValue]) Total Actual Value PM = CALCULATE( [Total Actual Value], PREVIOUSMONTH('DATE'[Date]) ) Total Actual Value MTM Change = [Total Actual Value] - [Total Actual Value PM] Total Actual Value MTM % Change = DIVIDE( [Total Actual Value MTM Change], [Total Actual Value PM], BLANK() )

If you drop those into a matrix you get this
You didn't specify if you were trying to get % change MTM, quarter to quarter or year to year so I went with MTM. If you want QTQ or YOY substitute PREVIOUSMONTH() with the appropriate time intelligence function.

Hope this helps!
Anonymous
Not applicable
5 years ago
Hi littlemojopuppy ; thank you for the solution. I did somhow still get stuck? Can you please please check the updated file here?:
https://1drv.ms/u/s!ApVe-eCmnWcTuF7RZzbrwkMOtY99?e=Fd9XwV

I am trying to get a chart with daily % changes along the actual values. Please help?
- littlemojopuppy
  Community Champion
  5 years ago
  Anonymous it looks like your data is summarized by month...you won't be able to calculate meaningful DTD changes
  - Anonymous
    Not applicable
    5 years ago
    littlemojopuppy I cannot understand what i am doing wrong. The % change chart is just not working out. Sorry if i am being difficult. 😕
Anonymous
Not applicable
5 years ago
I am also okay to settle for MTM change but even then it does not look right.
- littlemojopuppy
  Community Champion
  5 years ago
  Does this look better?
  In the PBIX I downloaded yesterday, you have the date in the x axis of the chart. You should do two things:
  Create a hierarchy of Year, Month and Date
  Make sure Month is sorted by MonthOfYear (in data view, select the column, Ribbon > Modeling > Sort by Column)
  Once the hierarchy is created, remove date from the axis and put the hierarchy there. Drill down one level to month.
  - littlemojopuppy
    Community Champion
    5 years ago
    By the way...you also should replace the axis of the bigger chart at the top left with the hierarchy as well. And change the date used in the slicer to the date field in the date table.
Anonymous
Not applicable
5 years ago
HI Anonymous,

Have you tried to use the date function to manually defined filter ranges to calculate? It should agility than time intelligence functions.

Total Actual Value MTM % Change = VAR currDate = MAX ( 'DATE'[Date] ) VAR curr = SUM ( 'Canada Retail Trade - PRIME'[ActualValue] ) VAR prev = CALCULATE ( SUM ( 'Canada Retail Trade - PRIME'[ActualValue] ), FILTER ( ALLSELECTED ( 'Canada Retail Trade - PRIME' ), [Date] >= DATE ( YEAR ( currDate ), MONTH ( currDate ) - 1, DAY ( currDate ) ) && [Date] <= currDate ) ) RETURN DIVIDE ( curr - prev, prev, BLANK () )

Regards,

Xiaoxin Sheng
- littlemojopuppy
  Community Champion
  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
  Not applicable
  5 years ago
  I will try this as well and let you know.
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
  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
    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
Community Champion
5 years ago
Base date table...use the CALENDARAUTO() function.

For more fields...
Year - add columnYEAR() function
MonthNumber - MONTH() function
MonthName - FORMAT(Calendar[Date], "MMMM")
Quarter field, QUARTER() function
Weekday - WEEKDAY() function
WeekdayName - FORMAT(Calendar[Date], "DDDD")
Remember to sort the MonthName and WeekdayName fields by the appropriate number fields
littlemojopuppy
Community Champion
5 years ago
Anonymous de nada...glad I could help!