Forum Discussion

netanel's avatar
netanel
Post Prodigy
4 years ago
Solved

Daily AVG

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 

Need Help
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    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.

2 Replies

  • Whitewater100's avatar
    Whitewater100
    Solution Sage
    4 years ago

    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])

    )

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Community Support
    4 years ago

    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.