topic Re: How to compare different years date in a single table and chart in DAX Commands and Tips

How to compare different years date in a single table and chart

thomsona — Wed, 16 Nov 2022 15:43:21 GMT

I am trying to create a report from the responses to a travel survey and need to be able to compare results from last year against those for this year.

Specifically I need to be able to show in one chart the changes in the amount of CO2 produced by various modes of transport from one year to the next.

I have tables for the journey data for both last year and this year with a measure that calculates the CO2 figure for the different modes of transport. The fields in both tables are the same.

At the moment the results of the calculation run through the measure are shown as two separate graphs in the report whereas it would be visually better to have them as a single graph.

How would I combine the two years worth of data to show the different CO2 emissions for each mode of transport as a single graph?

As the survey that gathers the journey data is run ever year it would be good to be able to understand how to do this so that the changes over a longer period of time can also be shown if required.

Any help with this would be most welcome.

Many thanks,

Andy

Re: How to compare different years date in a single table and chart

djurecic — Wed, 16 Nov 2022 23:28:42 GMT

Hi thomsona ,

You should be able to do this using a measure with SAMEPERIODLASTYEAR https://learn.microsoft.com/en-us/dax/sameperiodlastyear-function-dax

Re: How to compare different years date in a single table and chart

FreemanZ — Thu, 17 Nov 2022 05:22:51 GMT

Supposing you have a measure named [CO2Emission], then you can create a new measure for the emission in previous year like this:

CO2EmissionPY =

CALCULATE(

[CO2Emission],

DATEADD('Date'[Date], -1, YEAR)

)

the trick is the number -1, which means 1 year ago. If you change it to -2, then the result is previous previous year.

BTW, DATEADD is intelligence function, you would need a Date table.

Re: How to compare different years date in a single table and chart

thomsona — Mon, 21 Nov 2022 12:45:52 GMT

Many thanks for the suggested way forwards on this.

I have two measures (CO2 2021 and CO2 2022) which run the calculation for the respective year as the survey responses are not held in a single spreadsheet but rather in two seperate spreadsheets which I have not combined into a single dataset.

From what you have suggested this would work if the responses were in a single dataset and had a year identifier associated with each response (i.e. 2021 and 2022)?

It would not be difficult to set up a Date table as this can be expanded to include each your that the survey is run.

Re: How to compare different years date in a single table and chart

thomsona — Mon, 21 Nov 2022 12:56:01 GMT

Many thanks for pointing out that option as a possible way to solve being able to display a comparison between the two years worth of data.

From looking at this I would need to have a date associated with each response for the measure to be able to function correctly.

At present the dataset does not include this nor are the two years worth of data combined in a single dataset.

It has potential though and will continue to explore this as the way to do what I want to do.