Using Power BI to show change/improvement over time with separate excel datasets

[bond2340]

Hello, struggling to come up with an effective solution for the following issue.

I have an excel report that is is generated on a weekly basis with data on the number of calls (ie both incidents & requests), how long the call has stayed open, which team it is assigned to, which analyst it is assigned to & its current status.

Using the data, I have created visuals based on what I need to track (e.g a stacked column showing total number of aged calls split to requests and incidents based on the record type, aged calls split between periods of time open and which status they are in).

When the next report is generated, I can load this in as a separate query and change the source to show the current week.

However I need to show the change over time for each of these visuals, ideally show weekly changes as each report comes in for the previous month. 

Is there a way to compare the excel reports and show this change based on the visuals that I have already created? (i.e for the stacked column, the change would need to show the decrease or increase in total number of aged calls still split to requests and incidents based on the record type)

I have popped an example of the data generated below and some of the visuals that I have created for them.

Record ID	Record Type	Time Open (days)	Assigned Group	Assigned To	Status
1241583	Request	14	Test Operations		Test

Thank you!

[lbendlin]

When the next report is generated, I can load this in as a separate query and change the source to show the current week.

 

However I need to show the change over time for each of these visuals, ideally show weekly changes as each report comes in for the previous month. 

Add a snapshot date column to your source data and then append your queries. Then you can compare values across snapshots.

[bond2340]

Hello,

         Thank you.

I have added a snapshot date column as a new column to my initial data and to the 2nd report as well, with the relevant data for each one on the day that the report is generated and have appended the queries as well.

However when trying to compare the values across the snapshots using the date column, it still doesnt seem to track any increase or decrease in the values as it seems to stick to the existing filters.

[lbendlin · Accepted solution]

Good. Now implement the measures that calculate the snapshot-over-snapshot change.  You can do that in standard DAX (as a measure) or in the new Visual Calculations.