Average on or after a specific date

[jcastr02]

I have a file with store closing dates and daily sales....

I'd like to be able to see the average of the Daily Sales column only for data ON or AFTER the closure date.  Example for store 123, the Average of those 6 Dates is ~297, but for those 5 dates after 1/2/2025, the average is 305 =  (257+300+301+333+332)/ 5

Is there a measure that can do this?

See below sample data

Date	Str Number	Daily Sales	Closure Date	Avg After Closing Date
1/1/2025	123	256	1/2/2025
1/2/2025	123	257	1/2/2025	305
1/3/2025	123	300	1/2/2025
1/4/2025	123	301	1/2/2025
1/5/2025	123	333	1/2/2025
1/6/2025	123	332	1/2/2025
1/1/2025	456	100	1/3/2025
1/2/2025	456	101	1/3/2025
1/3/2025	456	125	1/3/2025	113
1/4/2025	456	110	1/3/2025
1/5/2025	456	111	1/3/2025
1/6/2025	456	105	1/3/2025
1/1/2025	789	501	1/4/2025
1/2/2025	789	525	1/4/2025
1/3/2025	789	565	1/4/2025
1/4/2025	789	525	1/4/2025	529
1/5/2025	789	530	1/4/2025
1/6/2025	789	531	1/4/2025
1/1/2025	101	700	1/5/2025
1/2/2025	101	705	1/5/2025
1/3/2025	101	702	1/5/2025
1/4/2025	101	701	1/5/2025
1/5/2025	101	694	1/5/2025	687
1/6/2025	101	680	1/5/2025
1/1/2025	102	100	1/6/2025
1/2/2025	102	108	1/6/2025
1/3/2025	102	107	1/6/2025
1/4/2025	102	106	1/6/2025
1/5/2025	102	105	1/6/2025
1/6/2025	102	115	1/6/2025	115

[DataInsights · Accepted solution]

@jcastr02,

Try this measure:

Avg After Closing Date = 
VAR vTable =
    CALCULATETABLE (
        SUMMARIZE ( 'Table', 'Table'[Str Number], 'Table'[Daily Sales] ),
        'Table'[Date] >= 'Table'[Closure Date]
    )
VAR vResult =
    AVERAGEX ( vTable, 'Table'[Daily Sales] )
RETURN
    vResult

[ThxAlot]

[DataInsights · Accepted solution]

@jcastr02,

Try this measure:

Avg After Closing Date = 
VAR vTable =
    CALCULATETABLE (
        SUMMARIZE ( 'Table', 'Table'[Str Number], 'Table'[Daily Sales] ),
        'Table'[Date] >= 'Table'[Closure Date]
    )
VAR vResult =
    AVERAGEX ( vTable, 'Table'[Daily Sales] )
RETURN
    vResult

[jcastr02]

Thank you @DataInsights 

Would you know also how to exclude dates where the Closure date field is blank.  It seems when there is no closing date listed its pulling those values in.  Thanks for your help. 

[DataInsights · Accepted solution]

@jcastr02,

Try adding an additional filter to exclude blank dates:

Avg After Closing Date =
VAR vTable =
    CALCULATETABLE (
        SUMMARIZE ( 'Table', 'Table'[Str Number], 'Table'[Daily Sales] ),
        'Table'[Date] >= 'Table'[Closure Date],
        NOT ISBLANK ( 'Table'[Closure Date] )
    )
VAR vResult =
    AVERAGEX ( vTable, 'Table'[Daily Sales] )
RETURN
    vResult