Forum Discussion

Luke_Howells's avatar
Luke_Howells
Icon for Helper I rankHelper I
4 years ago
Solved

Ignore Blanks in Measure Result

Hi All, I have a measure below which calculates the number of hours lost over a date range but also includes the 3 months prior based on the earliest date selected in the date filter. I am trying to get an average result which it is producing but also includes months where there are no hours lost. I only want the result to show an average where there are months with hours lost. Any ideas?

 

 

This is the measure being used. 

 

Average Hours Lost Over Date Range and 3 Months = CALCULATE(
                SUM('07 - COVID Absence Record'[Hours Lost (4)]),
                    DATESBETWEEN('01. Date Table'[Date],
                        FIRSTDATE('01. Date Table'[Date Minus 3 Months]),
                            LASTDATE('01. Date Table'[Date])))

/

ROUNDDOWN(((ROUND(LASTDATE('01. Date Table'[Date]) - FIRSTDATE('01. Date Table'[Date Minus 3 Months]),0)) / 7),0)

 

 

Need Help
Show and Tell
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    Hi, Luke_Howells ;

    Is your problem solved?? If so, Would you mind accept the helpful replies as solutions? Then we could close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.

    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, Luke_Howells ;

     

    You can modify your measure by adding filter ('07 - COVID Absence Record'[Hours Lost (4)]<>blank()),
    Or additionally create a measure to count the total number of non-zero months;

     

    Or Could you please consdier sharing a sample file without any sesentive information and posting expected result so it is clear on your logic?
    It makes it easier to give you a solution.

     

    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, Luke_Howells ;

    Is your problem solved?? If so, Would you mind accept the helpful replies as solutions? Then we could close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.

    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.