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

     

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

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's avatar
DataInsights
Icon for Super User rankSuper User
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