Measure ignoring zero values

[dtgold81]

Hi all,

I've got a weird problem with a measure in a matrix. In essence, I want to calculate the change in 'Total Entries' between two fortnights.

I have a measure which calculates the total entries:

Total Entries = CALCULATE (DISTINCTCOUNT ( 'Chronicle Items'[Entry_Student_ID] ))+0

And another measure which calculates the differences between the 'Total Entries' from the earliest fortnight to the latest fortnight:

Difference Between Earliest and Latest Totals (Chronicle Items) =

    CALCULATE (

        [Total Entries],

        FILTER ( 'Chronicle Items', 'Chronicle Items'[Fortnight commencing] = MAX ('Chronicle Items'[Fortnight commencing] ) ))

    - CALCULATE (

        [Total Entries],

        FILTER ( 'Chronicle Items', 'Chronicle Items'[Fortnight commencing] = MIN ('Chronicle Items'[Fortnight commencing] ) ))
    +0

This works perfectly UNLESS one of the 'Total Entries' values is zero. See matrix below for example (green shows expected behaviour, red shows the problem - in the highlighted example, it should show '1' in the total change column (far right).

I've tried setting both measures to 'Decimal' number type, but that didn't help. I'm all out of ideas.

Any ideas what might be causing this?

Thanks for your help!

[Daniel29195 · Accepted solution]

@dtgold81 

i think that problem with your code is that example the line you highlighted in red, 

im assunmming that there is no row for this line item in 26/02.2023, 

so in the total, 

your code will return for max and min  --> 12/03/2023 which is 1 - 1 = 0..  and that is why you are getting such results .

If my response has successfully addressed your issue kindly consider marking it as the accepted solution! This will help others find it quickly. Dont forget to hit that  thumbs up button 🫡👍

[Ritaf1983]

Hi @dtgold81 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

 If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly