Forum Discussion

jcastr02's avatar
jcastr02
Icon for Post Prodigy rankPost Prodigy
1 year ago
Solved

Average 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 ...
Need Help
  • DataInsights's avatar
    DataInsights
    1 year ago

    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

     

     

  • DataInsights's avatar
    DataInsights
    1 year ago

    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