cancel
Showing results for
Did you mean:
Frequent Visitor

## Year on Year data comparison

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??

1 ACCEPTED SOLUTION

Accepted Solutions
Super Contributor

## Re: Year on Year data comparison

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

5 REPLIES 5
Super User

## Re: Year on Year data comparison

Please check this blog at HERE to calculate YoY difference.

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Frequent Visitor

## Re: Year on Year data comparison

Thank you Bhavesh.

Warm regards

Malc

Super Contributor

## Re: Year on Year data comparison

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

Frequent Visitor

## Re: Year on Year data comparison

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

Super Contributor

## Re: Year on Year data comparison

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