Daily AVG

[netanel]

Hi All!

I have this maesure:

Net USD AVG =

CALCULATE(

DIVIDE( SUM( 'DB 2022'[Net USD] ), COUNTROWS( 'Date' ) ),

keepfilters( 'Date'[Date] < TODAY())

)

It works great
It divides the Data on the current day of the month for example the 16th of January so it will only divide by 16 days
My problem starts that sometimes the Data is only up to 12 or less and then the Divide is incorrect

How do I change the formula so that it only divides the number of days in the existing Data?

Thanks 

[littlemojopuppy · Accepted solution]

Hi @netanel.  This should calculate a daily average for all dates in the filter context

Daily Average =
AVERAGEX(
	VALUES([Your date field]),
	SUM('DB 2022'[Net USD])
)

Hope this helps!

[littlemojopuppy]

@netanel awesome!  I'm glad that what I suggested you try ended up being the solution!  😊

[netanel]

@littlemojopuppy 

Who defined this as a solution?
Nothing was resolved
And I did not define it as a solution ...

[littlemojopuppy]

@netanel you said that you overcame your issue with the solution in the pic you supplied, which was exactly what I suggested.  The other issue you mentioned is from information not included in the initial request...

[netanel]

@littlemojopuppy 

Maybe I accidentally clicked on Get a solution and iam sorry for that.

But in the picture you can see that I get the 31st per month even though I am only on the 18th of the month
Or my data is only up to the 16th of the month
This is one problem

Problem number two
When rising to a monthly or quarterly level there is no calculation of average at all

[littlemojopuppy · Accepted solution]

Hi @netanel.  This should calculate a daily average for all dates in the filter context

Daily Average =
AVERAGEX(
	VALUES([Your date field]),
	SUM('DB 2022'[Net USD])
)

Hope this helps!

[netanel]

@littlemojopuppy 

Hi thanks for the response,

There are two problems
1. He also brings data from the 31st day of the month even though I am on the 17th of the month

2. As soon as I go up in a hierarchy to the level of a month it is summed up all the days of the month and then
If I earned 5000 a day then the average monthly level becomes 30 times 5000
And the average should remain 5000
That is, when I go up to the monthly and quarterly level he sums up all the amounts

thanks

[littlemojopuppy]

@netanel I don't understand how that figures into the problem.  Can you provide additional info?

[netanel · Accepted solution]

Hi @littlemojopuppy  thanks for the response!

Problem number 1:

Problem number 2:

I can not share my data but
Problem number 2 is simple and I overcame it in my formula above
The problem is that if every day I get 5000
So when I go up to the monthly level instead of getting an average of 5000 for January I get 31 times 5000 that it comes out to 155,000
And if I go up to the quarter level then I get 155K double 3