cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Calculate Average Using Dates from a Measure

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: 
 
LocationID20152016201720182019
1 14/04/201615/03/201715/03/201701/04/2019
201/06/201501/06/201529/05/201703/06/201803/06/2018
3  11/11/201711/11/201714/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

1 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.

View solution in original post

4 REPLIES 4
Super User III
Super User III

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 

SU18_powerbi_badge

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:

 

ElChambre20_0-1602495523133.png

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

@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 

 

SU18_powerbi_badge

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.

View solution in original post

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors