Forum Discussion

KatkaS's avatar
KatkaS
Icon for Post Patron rankPost Patron
5 years ago
Solved

Exclude blanks

Hello could anyone please hlep me with following? I have calculated column of days excluding week-ends, which is working fine if Approved date and Closed Date are defined, but not if they are blank...
Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    5 years ago

    Try something like this.

     

    Avg Days =
CALCULATE (
    AVERAGE ( 'Report'[# Days] ),
    'Report'[# Days] <> 0
)

     

    Or you could change the [# Days] to return blank instead of 0 which will not get counted in an average.

     

    # Days =
IF (
    ISBLANK ( 'Report'[Approved Date] ) || ISBLANK ( 'Report'[Closed Date] ),
    BLANK(),
    CALCULATE (
        SUM ( 'CALENDAR'[Day] ),
        DATESBETWEEN (
            'CALENDAR'[Date],
            'Report'[Approved Date],
            'Report'[Closed Date]
        )
    )
)

     

     

jdbuchanan71's avatar
jdbuchanan71
Icon for Super User rankSuper User
5 years ago

KatkaS 

Give this a try.

# Days =
IF (
    ISBLANK ( 'Report'[Approved Date] ) || ISBLANK ( 'Report'[Closed Date] ),
    0,
    CALCULATE (
        SUM ( 'CALENDAR'[Day] ),
        DATESBETWEEN (
            'CALENDAR'[Date],
            'Report'[Approved Date],
            'Report'[Closed Date]
        )
    )
)

 

KatkaS's avatar
KatkaS
Icon for Post Patron rankPost Patron
5 years ago

Thank you, so much!!!

I also need to do an averge of those days, but it is also taking in consideration 0 days (which is technically correct, but not practically - these 0 days requests are not yet closed, so they should not be counted in). I tried to filter them out, but it is not working.. Would you happen to know how to filter them out (I tried in the Filter itself and by adding FILTER function to your above, but none is working...):

Thank you again..

 

  • jdbuchanan71's avatar
    jdbuchanan71
    Icon for Super User rankSuper User
    5 years ago

    Try something like this.

     

    Avg Days =
CALCULATE (
    AVERAGE ( 'Report'[# Days] ),
    'Report'[# Days] <> 0
)

     

    Or you could change the [# Days] to return blank instead of 0 which will not get counted in an average.

     

    # Days =
IF (
    ISBLANK ( 'Report'[Approved Date] ) || ISBLANK ( 'Report'[Closed Date] ),
    BLANK(),
    CALCULATE (
        SUM ( 'CALENDAR'[Day] ),
        DATESBETWEEN (
            'CALENDAR'[Date],
            'Report'[Approved Date],
            'Report'[Closed Date]
        )
    )
)

     

     

    • KatkaS's avatar
      KatkaS
      Icon for Post Patron rankPost Patron
      5 years ago

      It worked, thank you so much!!!