Forum Discussion

ElChambre20's avatar
ElChambre20
Frequent Visitor
5 years ago
Solved

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 ...
Need Help
  • ElChambre20's avatar
    ElChambre20
    5 years ago

    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.

AlB's avatar
AlB
Community Champion
5 years ago

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's avatar
ElChambre20
Frequent Visitor
5 years ago

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's avatar
    AlB
    Community Champion
    5 years ago

    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's avatar
      ElChambre20
      Frequent Visitor
      5 years ago

      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.