Forum Discussion

jcastr02's avatar
jcastr02
Post Prodigy
1 year ago

Exclude Blanks in Measure

Is there a way to tailor this measure so that it excludes blanks in the "Closure Date" column? Avg After Closing Date = VAR vTable = CALCULATETABLE ( SUMMARIZE ( 'Table', 'Table'[St...
Need Help
danextian's avatar
danextian
Super User
1 year ago

Hi jcastr02 

Is this a measure and you want to exlude blank closure date from calculating the average? Try:

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