Year on Year data comparison

[mguy61]

I have a simple table of survey data arranged in rows with one resonse per row, the total number of rows equalling the total number of reponses. There is a Year column containing either 2015 or 2016 and a question column indicating the survey question. I want to be able to calculate the difference in the number of responses (rows) between 2015 and 2016 for each question, or group of questions.

I then want to plot the year on year difference on a graph where a decrease shows beneath the X axis. I guess I need to create a measure but can't find any help in how to do this. Can you help??

[v-huizhn-msft]

Hi @mguy61,

For your reguriement, please post more details or sample data for further analysis. If you find the solution, please share or mark the correnponding reply as answer for help others easily. Thanks a lot.

Best Regards,
Angelia

[mguy61]

Hi Angelia

We make observations on farms when we see something wrong, looking at over 40,000 farms. Each observation is logged against a category (also Farmd ID, Auditor name, location, month, year). The table below is a simplified example showing just the year. Each row corresponds to an observation. 

Observation category	Year
Health and Safety	2015
Wages	2015
Working Hours	2015
Health and Safety	2015
Wages	2015
Wages	2015
Working Hours	2016
Wages	2016
Health and Safety	2016
Health and Safety	2016
Wages	2016
Working Hours	2016

What I want to do is to create a table within PowerBI that calculates the difference in the number of observations for 2016 (COUNTROWS??) compared with 2015, for each observation category. So the resultant table would look something like this:

Observation category	2016 - 2015
Health and Safety	0
Wages	-1
Working Hours	1

I would want to visualise the data by auditor name, location etc, using the calculated table.

I have been trying to create a calculation by creating a New Table and applying filters but have not had any success.

Thanks

Malcolm

[v-huizhn-msft · Accepted solution]

Hi @mguy61，

Based on your description, you can use COUNTROWS or COUNTA function to calculate the number of category in each year. I try to reproduce your scenario using sample data and get expected result.

Please create a measure using the formula below.

this-last = CALCULATE(COUNTA(Test1[Observation category]),FILTER(ALL(Test1[Year]),Test1[Year]=MAX(Test1[Year])))- CALCULATE(COUNTA(Test1[Observation category]),FILTER(ALL(Test1[Year]),Test1[Year]=MAX(Test1[Year])-1))

Create a table, select the Observation category and measure as values, you will get the desired result.



Best Regards,
Angelia

[BhaveshPatel]

Please check this blog at HERE to calculate YoY difference.

[mguy61]

Thank you Bhavesh. 

Warm regards

Malc