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 that using DAX?

I tried DATESBETWEEN as shown, with no success.

 

 

average
DAX

3 Replies

  • 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.