Forum Discussion
% 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...
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.
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
I will try this as well and let you know.