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 18th of January so it will only divide by 18 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 

[v-chenwuz-msft · Accepted solution]

Hi @netanel ,

This filtered context used by this measure is not the same as you want.

Try to remove some filters which has effected this measure's context. I mean replace the keepfilters() by allexcpet() or allselected() .

Try some code like the following:

Net USD AVG =
CALCULATE(
    DIVIDE( SUM( 'DB 2022'[Net USD] ), COUNTROWS( 'Date' ) ),
    ALLEXCEPT( 'Date', 'Date'[month] ),
    // OR filter(all('Date'),month([Date]) = month(today()) ),
    'Date'[date] <= TODAY()
)

Best Regards

Community Support Team _ chenwu zhu

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

[Whitewater100]

Hello:

Can you try:

AVERAGEX(VALUES('Date'[Date]), 
[Net USD])

You can add conditional statements before it like:

IF( [Net USD] > 0, 
AVERAGEX(VALUES('Date'[Date]), 
[Net USD])

)