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 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 |
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
4 Replies
- DataInsights
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
jcastr02Thank 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
- ThxAlot
