Forum Discussion

KatkaS's avatar
KatkaS
Post 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.

Could someone help me how to solve this? e.g. return 0 if Approved or Closed are blank...?

# Days = CALCULATE(SUM('CALENDAR'[Day]),DATESBETWEEN('CALENDAR'[Date],'Report'[Approved Date],'Report'[Closed Date]))
 
Thank you very much!

 

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]
        )
    )
)

     

     

4 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Super 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
      Post 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
        Super 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]
        )
    )
)