Forum Discussion
jcastr02
Post Prodigy
Post ProdigyAverage on or after a specific date
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 ...
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 vResultTry 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
DataInsights
Super User
Super User
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
jcastr02Post Prodigy
Post ProdigyThank 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
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