cancel
Showing results for
Did you mean:
Helper I

## Average of values on specific dates only

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

1 ACCEPTED SOLUTION
Community Support

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#M6420...

-

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.

3 REPLIES 3
Community Support

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:

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.

Helper I

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.

Community Support

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#M6420...

-

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.

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

#### Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors