Forum Discussion

Thimios's avatar
Thimios
Icon for Helper III rankHelper III
3 years ago

Avg calculation filtered for existing data

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 th...
average
DAX
johnt75's avatar
johnt75
Icon for Super User rankSuper User
3 years ago

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
  • Thimios's avatar
    Thimios
    Icon for Helper III rankHelper III
    3 years ago

    Thank you johnt75 ,

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

     

    • johnt75's avatar
      johnt75
      Icon for Super User rankSuper User
      3 years ago

      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.