Average of values on specific dates only

[Anonymous]

Hey cummunity,

I am trying to calculate the average of GAV, but it has to count only the values of GAV on the last day of every month in the selected range (see picture below). The rusult I am getting is slightly of.

According to my calaculations it should be  299 174 307,14 for year 2020 and 326 112 602,05 for 2021. The formula I am using is:
 

Average GAV =

VAR MaxMonth = CALCULATE(MAX(Dates[MonthNum]), ALL(Dates), Dates[CurMonthOffset] = 0)

RETURN

CALCULATE(AVERAGEX( VALUES(Dates[EndOfMonth]), [GAV] ), Dates[MonthNum] <= MaxMonth, FILTER(Dates, ALLSELECTED(Dates[Year])), ALL('IslaiduLentele Map'))

 Do you have any suggestions on how I could change it ?

I am ataching an example PBI file and my gratitude :

https://www.dropbox.com/s/52co15sbnqnk0ka/Example4.pbix?dl=0

[v-xiaotang]

Hi @Anonymous 

The description of the problem is not very clear. 

year=2021,  AVG=326 112 602,05;

year=2020,  AVG=299 174 307,14; 

but how is this calculated? please let me know whose average you want to calculate.

the average value of the data in the picture is... Obviously it's not the result you want. so please put the true picture with data where your average comes from.

FYI:

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1579748#M642005

Best Regards,

Community Support Team _ Tang

If this post helps, please consider Accept it as the solution✔️ to help the other members find it more quickly.

[Anonymous]

Thank you for your answer and patience 🙂 

The range described in the picture is correct, what is wrong is the expected values I sent you. 

year=2021,  AVG = 27 176 050,17;

year=2020,  AVG = 27 197 664,29;

Sory for the mistake.

[v-xiaotang · Accepted solution]

OK @Anonymous 

but when year=2020, avg should be 24931192.2616667 (Not 27 197 664,29)  if your expected data could be accurate, then we can work on it quickly.🤣  FYI: https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1579748#M642005

-

use the measure bellow,

Measure_average =
VAR _count =
    CALCULATE ( DISTINCTCOUNT ( Dates[EndOfMonth] ), ALLSELECTED ( Dates ) )
VAR _sum =
    SUMX ( SUMMARIZE ( Dates, Dates[EndOfMonth], "s1", [GAV] ), [s1] )
RETURN
    _sum / _count

result:

Best Regards,

Community Support Team _ Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.