% change from previous time stamp

[Anonymous]

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 

[littlemojopuppy]

@Anonymous de nada...glad I could help!

[Anonymous]

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]

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

[Anonymous]

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

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]

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]

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]

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]

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?

[Anonymous]

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

[Anonymous]

I will try this as well and let you know.

[littlemojopuppy · Accepted solution]

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

I am also okay to settle for MTM change but even then it does not look right.

[littlemojopuppy]

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:

1. Create a hierarchy of Year, Month and Date
2. 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.

[Anonymous]

So i tried doing this and things went crazier than normal. Maybe i could try remaking the Calendar table using some good dax that you may have? My date hierarchy is being difficult.

[littlemojopuppy]

Hi @Anonymous 

Download what I did from here 😊

[Anonymous]

I have requested access

[littlemojopuppy]

Hi @Anonymous I was in a doctor's office when I saw you requested access.  Were you able to download it?

[littlemojopuppy]

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]

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?