Ignore Blanks in Measure Result

[Luke_Howells]

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)

[v-yalanwu-msft · Accepted solution]

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.

[v-yalanwu-msft]

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.

[Samarth_18]

Hi @Luke_Howells ,

Have you tried to use is not blank filter on your visual.

Thanks,

Samarth