Calculate Average Using Dates from a Measure

[ElChambre20]

Hi All,

Im relatively new to Power BI (only been using for 5 months).

Im dealing with data relating to site surveys, and each survey is assigned a score based on its quality.

I have been asked to track the average score over time which was simple enough to do using the following measure:

AT SURVEY ACC_2 =

CALCULATE(

AVERAGE(

CalcTableSiteVisitsWithRatings[AtSurveyRiskRatingReverseRank]

),

FILTER(

ALLSELECTED('Date'),

'Date'[Date]<=MAX('Date'[Date])

)

)

This worked fine until i was advised we do not want to include repeat surveys. So at any point in time what was the average survey score of the most recent surveys carried out.

So i figured the first thing i needed to do was work out what was the date of the last survey carried out depending on the year and came up with this measure:

AT SURVEY LAST_SURVEY =

CALCULATE(

MAX(

CalcTableSiteVisitsWithRatings[EventDate]),

FILTER(

ALLSELECTED('Date'),

'Date'[Date]<=MAX('Date'[Date]

)

)

)

As an example this is what that measure returns when i put it in a matrix: 

LocationID	2015	2016	2017	2018	2019
1		14/04/2016	15/03/2017	15/03/2017	01/04/2019
2	01/06/2015	01/06/2015	29/05/2017	03/06/2018	03/06/2018
3			11/11/2017	11/11/2017	14/10/2019

As you can see its doing exactly what i want, in each year its returning the most recent survey date for each loaction upto that year. What i need to do now is return the survey scores for those surveys so i can average them. This is where i have become completely stuck. The end goal is to plot the average survey score over time on a line graph but only every using the most recent survey carried out.

I have tried calculating the average of the score where the event date = the measure above but it just returns no data whatsoever.

Sorry this was rather long winded and if i havent explained myself very well please let me know and i'll provide any further info required.

Thanks in advance

[AlB]

Hi @ElChambre20 

One option would be to create a filtered down version of your original table, selecting only the rows that have the latest survey for each year (for each location ID, if I understand correctly). Then run an AVERAGEX on that table. Something along the lines of:

Measure =
CALCULATE (
    AVERAGEX (
        FILTER (
            CalcTableSiteVisitsWithRatings,
            VAR currentYear_ =
                YEAR ( CalcTableSiteVisitsWithRatings[EventDate] )
            VAR latestInYear_ =
                CALCULATE (
                    MAX ( CalcTableSiteVisitsWithRatings[EventDate] ),
                    YEAR ( CalcTableSiteVisitsWithRatings[EventDate] ) = currentYear_,
                    ALLEXCEPT (
                        CalcTableSiteVisitsWithRatings,
                        CalcTableSiteVisitsWithRatings[LocationID]
                    )
                )
            RETURN
                IF (
                    CalcTableSiteVisitsWithRatings[EventDate] = latestInYear_,
                    TRUE (),
                    FALSE ()
                )
        ),
        CalcTableSiteVisitsWithRatings[AtSurveyRiskRatingReverseRank]
    ),
    FILTER ( ALL ( 'Date' ), 'Date'[Date] <= MAX ( 'Date'[Date] ) )
)

which follows the logic of your first measure but adding the filtering on the base table for the AVERAGEX to select only the latest surveys per year. I would need data to run a quick test. You can tweak it yourself as necessary.

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[ElChambre20]

Thanks @AlB ,

Thanks for the measure you wrote, i  certainly learnt a thing or 2 about filtering the table before calculating the average so that has been really useful.

Unfortunately, I've been playing about with the measure you provided for a few hours and unfortunately it is still returning the average of all surveys up to and including the current year, not just the last survey:

If you see, on the second row, the 2017 survey had a score of 4, so i would want it to show 4 here, but it is showing 3.5 as that is the average of both the 2016 (Score 3)  and 2017 (Score 4) surveys.

I've tried tweaking your measure myself but im afraid i havent really got anywhere with it. I tried adjusting it to instead of working out the average, just return a "1" where the eventdate was the latest date (Using a SUMX instead of AVERAGEX) and then use that as a filter, but it didnt work either.

Any other help you can provide would be greatly appreciated.

Thanks

[AlB]

@ElChambre20 

I would need some sample data to test the measure on then. And a clear explanation of what the expected result is on that sample data and why. I'm not sure I've fully understood the requirements.

Please provide your sample data in text-tabular format in addition to (or instead of) the screen captures so that it can be readily copied.

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[ElChambre20 · Accepted solution]

Hi @AlB ,

After tweaking your measure vary slightly i got it to work!

AT SURVEY ACC_3 = 
CALCULATE (
    AVERAGEX (
        FILTER (
            CalcTableSiteVisitsWithRatings,
            VAR currentYear_ =
                CALCULATE(
                    MAX ( 'Date'[Date] ),
                    ALLSELECTED('Date')
                )
            VAR latestInYear_ =
                CALCULATE (
                    MAX ( CalcTableSiteVisitsWithRatings[EventDate] ),
                    CalcTableSiteVisitsWithRatings[EventDate]  <= currentYear_,
                    ALLEXCEPT (
                        CalcTableSiteVisitsWithRatings,
                        CalcTableSiteVisitsWithRatings[LocationID]
                    )
                )
            RETURN
                IF (
                    CalcTableSiteVisitsWithRatings[EventDate] = latestInYear_,
                    TRUE (),
                    FALSE ()
                )
        ),
        CalcTableSiteVisitsWithRatings[AtSurveyRiskRatingReverseRank]
    ),
    FILTER ( ALL ( 'Date' ), 'Date'[Date] <= MAX ( 'Date'[Date] ) )
)

I changed currentYear_ to work out the last date in the year from the Date table instead of using the eventdate, then changed latestInYear to MAX the dates up to and including currentYear_

Im now getting the desired result. Thankyou so much for your help, you were amazingly close considering my less than ideal description of the problem 🙂 Ive also learnt alot from playing around with your measure.