Avg calculation filtered for existing data

[Thimios]

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.

[johnt75]

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]

Thank you @johnt75 ,

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

[johnt75]

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.