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'[Str Number], 'Table'[Daily Sales] ),
        'Table'[Date] >= 'Table'[Closure Date]
    )
VAR vResult =
    AVERAGEX ( vTable, 'Table'[Daily Sales] )
RETURN
    vResult

 

Need Help

3 Replies

  • 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

     

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    1 year ago

    Hi, 
    I am not sure if this is a measure or a calculated column, but please try something like below whether it suits your requirement.

     

    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
    IF ( NOT ISBLANK ( 'Table'[Closure Date] ), vResult )

     

    • jcastr02's avatar
      jcastr02
      Post Prodigy
      1 year ago

      Jihwan_Kim thanks for quick reply this is a measure.  It seems it's not allowing me to add the column closure date in the return portion.