topic Re: Avg calculation filtered for existing data in DAX Commands and Tips

Avg calculation filtered for existing data

Thimios — Wed, 09 Aug 2023 05:26:25 GMT

Hi all,

I need to calculate the Average Sales only for the period that Sales data exist (red line).

The measure I created though expands to all dates in my Date table (blue line).

How can I fix that using DAX?

I tried DATESBETWEEN as shown, with no success.

Re: Avg calculation filtered for existing data

johnt75 — Wed, 09 Aug 2023 10:04:17 GMT

Try

Sales avg all dates = VAR FirstSale = CALCULATE ( MIN ( 'Sales'[Date] ), REMOVEFILTERS ( 'Dates' ) ) VAR FirstVisibleDate = EOMONTH ( FirstSale, -1 ) + 1 VAR LastSale = CALCULATE ( MAX ( 'Sales'[Date] ), REMOVEFILTERS ( 'Dates' ) ) VAR LastVisibleDate = EOMONTH ( FirstSale, 0 ) VAR MinDate = MIN ( 'Dates'[Date] ) VAR MaxDate = MAX ( 'Dates'[Date] ) VAR Result = IF ( MinDate >= FirstVisibleDate && MaxDate <= LastVisibleDate, CALCULATE ( AVERAGEX ( VALUES ( 'Dates'[Month & Year] ), [Sales] ), REMOVEFILTERS ( 'Dates' ) ) ) RETURN Result

Re: Avg calculation filtered for existing data

Thimios — Wed, 09 Aug 2023 10:15:31 GMT

Thank you johnt75 ,

Suggested measure works as far as calculation is concerned, but not as expected in visual.

Re: Avg calculation filtered for existing data

johnt75 — Wed, 09 Aug 2023 11:13:45 GMT

Use Performance Analyzer to copy the query for the visual into DAX Studio. You can then use the Define Measure feature in DAX Studio to change the measure to return each of the intermediate variables in turn, that should identify which variable is being calculated incorrectly.