Forum Discussion
Post ProdigyDont Bring Blank
This is a better measure replacing the >0
Net USD AVG = VAR NumDays= CALCULATE ( DISTINCTCOUNT ('DB 2022'[Date] ), KEEPFILTERS( 'Date'[Date] < TODAY() ), NOT ISBLANK ( 'DB 2022'[Net USD] ), REMOVEFILTERS ( 'SORT GL' ) ) VAR SumNet = CALCULATE ( SUM ( 'DB 2022'[Net USD] ), KEEPFILTERS( 'Date'[Date] < TODAY() ) ) RETURN DIVIDE (SumNet, NumDays)In your 'DB 2022' table you have rows for every date whether there is a value for [Net USD] or not. My original measure went for >0 just to make sure it was picking days with values. The above version is better in case you did get negative values.
The reason it wasn't working when the sort field is added in is there are days in each month when there are no values for that particular sort. It was therefore not including that day in the division for that section. By using the REMOVEFILTERS ( 'Sort GL' ) it says no matter what sort is selected divided by the total number of days in the whole period.
Community ChampionWould this work: (Hard to test without a demo file)
Net USD AVG =
CALCULATE(
DIVIDE(
SUM( 'DB 2022'[Net USD] ),
DISTINCTCOUNT( 'DB 2022'[Date] )
),
KEEPFILTERS( 'Date'[Date] < TODAY() )
)
Replace 'DB 2022'[Date] with the date column on your table.
Community ChampionOr depending on how big your fact table is this might be faster:
Net USD AVG =
CALCULATE(
AVERAGEX (
VALUES ( 'Date'[Date] ),
CALCULATE ( SUM ( 'DB 2022'[Net USD] ) )
),
KEEPFILTERS( 'Date'[Date] < TODAY() )
)
