Store calculated measure values from multiple data sources in a single table

[MarlonK]

Hello,

I have a data source on service tickets that I pull on the last day of each month. I do this so that I can perform analysis on the state of the data at that time, and compare it to the state that the data was in at the end of the previous month. 

Example:

Run the report on 7/31/20, find that 10 out of 15 open tickets are more than a week old. Run the report on 8/31/20, find that 6 out of 9 open tickets are more than a week old. Now I can calculate the change in % of tickets that are more than a week old from July to August. 

All good there, no problem... but I'd like to be able to show year over year change by month as opposed to just change from the previous month, which means that I'd need to be able to put together the % of tickets more than a week old from each run of the report over the past year, so that I can plot it on a line graph. 

One solution I thought of is to just append all the data sources together, and then write the calculations based on the date of the reports, but I was hoping there might be a way to just pull the calculated measure values from each data source into a single table and then plot the line graph based on that.

Example:

Data source from 5/31/20: % of tickets >= 7 days old = 66%

Data source from 6/30/20: % of tickets >= 7 days old = 76%

Data source from 7/31/20: % of tickets >= 7 days old = 88%

Data source from 8/31/20: % of tickets >= 7 days old = 55%

Pull those values into a table:

File Date	Percentage
5/31/2020	66
6/30/2020	76
7/31/2020	88
8/31/2020	55

...then use those values to plot a line graph.

Is there a way to accomplish this, and if so is it also possible to automatically add the new data each month?

Thank you!

[V-pazhen-msft · Accepted solution]

@MarlonK 

What I am thinking is to create a Calender table first. Once you connected to those datasources, set up a relationship between the Calender date and date in each source. You can create measure for each source to calculated the percent difference between the latest month and the month last year. Then put the calendar date and measures in a graph.

For example datasource 1,  the measure can be something like.

Measure datasource1 = 
var thisyear_month = calculate(sum(Source1[Tickets]),filter(Allselected(Source1), [Date].[Month.NO]=MAX([Date].[Month.no])&&[Date].[Year]=MAX([Date].[Year]))

var lastyear_month = calculate(sum(Source1[Tickets]),filter(Allselected(Source1), [Date].[Month.NO]=MAX([Date].[Month.no])&&[Date].[Year]=MAX([Date].[Year])-1))

Return = thisyear_month - lastyear_month

For datasource 1,  measure can be something like this, you can just replace fitler expression from MAX(date)  to some specific month and year. 

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MarlonK]

Thanks Paul, this seems like a great solution!