Compare selected month average to year to date average

[kattlees]

If someone can point me in the right direction.

I have a table for survey results.  What I want to do is show current month average score to year to date average score.

I have in survey table
date of procedure     Question 1   Question 2  Question 3 Overall

10-1-17                          5                  4                  5               5

10-1-17                           3                 3                   3               3

10-2-17                                              5                  5                 5

What I have is a card that shows the average of Question 1, a card that shows the average of Question 2, one for Question 3 and one for overall.

I have a slicer where I choose the month and the averages adjust from there.

What I want to do is show the Year to Date average next to the selected month's average. So if someone selects Sept 2017 it would show septembers values and the average through Sept. (not count october's in there). If they choose Sept 2016, it would show averages for Sept 2016 and the average for 2016 through September.

Any tips or point me in the direction of some reading?

[emadrigal · Accepted solution]

you need to create a measure to calculate the global average and then create a new meausure where you calculate sameperiodlast year using the calculate formula and same period last year:

example:

average = calculate(average(question),sameperiodlastyear(date var))

[kattlees]

So I created a measure of:

average = calculate(average('InPatient-Surveys'[1. Pre-Admission Experience]),sameperiodlastyear(PDate[Year]))

1. Pre-Admissions Experience is the field for Question 1

PDate is a table for date info and pulls distinct Date of Procedure from InPatient-Surveys

Year is a column in PDate table that is shows as Year = PDate[Date of Procedure:].[Year] (Whole number is field type)

In measure above, I get the error of "couldnt' load data for this visual.. a column specified in the call to function (SAMEPERIODLASTYEAR) is not of type DATE.

if I change it to type DATE and choose yyyy as format, all the years change to 1905.

[emadrigal]

you need to pass the date field on the formula sameperiodlastyear instead of year because is an integer and not a date. another suggestion is that you should convert your pivot to display question and values on rows so you can create only one measure

[kattlees]

So I just caught an issue - I dont' want same period last year - i want year to date for the current year.

[emadrigal]

drag a slicer with years and filter the year with only the average

[kattlees]

Ok - putting the date in there worked. Thank you...

I am not following your second suggestion though. "Convert your pivot to display question and values on rows"

Survey results are entered in an excel sheet (we are working on computerizing them).