Another Matrix Sum Issue

[Adams_Apple]

Hey guys, Really could use some help on this one.
Im calculating the "packing hours" of the factory line per shift and day. I have to do this by determining min and max times for each shift, which works great. I also have the measure totals for each shift and day worked out. but the grand total is giving me trouble. seems like it ought to be the easy part to figure out, but im not sure what im doing wrong. thanks!

Packing Hours =

var dayPH = CALCULATE(DATEDIFF(MIN(data_Bins[DateTime Dumped]),MAX(data_Bins[DateTime Dumped]),MINUTE) / 60,

    FILTER(data_Bins, data_Bins[Shift] = "1"))

var nightStart = CALCULATE(MIN(data_Bins[DateTime Dumped]), DATEDIFF(TIME(0,0,0),data_Bins[Time Dumped],HOUR) > 12)

VAR SelectedDate = SELECTEDVALUE(data_Bins[Date Dumped])

var nightEndDate = IF(

    NOT ISBLANK(SelectedDate),

    SelectedDate + 1,

    BLANK()

)

var nightStop = CALCULATE(

    MAX(data_Bins[DateTime Dumped]),

    FILTER(

        ALL(data_Bins),

        data_Bins[Date Dumped] = nightEndDate),

        DATEDIFF(TIME(0,0,0),data_Bins[Time Dumped],HOUR) < 3

    )

var nightPH = DATEDIFF(nightStart, nightStop, MINUTE) / 60

RETURN

    IF(ISINSCOPE(ref_Shifts[Short Name]),

        IF(SELECTEDVALUE(ref_Shifts[Short Name]) = "Day", dayPH, nightPH),

        dayPH + nightPH)

[v-mdharahman]

Hi @Adams_Apple, 

Thanks for reaching out to the Microsoft fabric community forum.

The issue you are running into with the grand total is a common one when using measures that rely on row context like "SELECTEDVALUE()" and date-based filtering.

What’s happening is that at the total level, Power BI evaluates your measure without a specific date or shift in context. That means "SELECTEDVALUE(data_Bins[Date Dumped])" returns blank when multiple dates are selected, which causes your nightEndDate and nightPH to break or return unexpected results. Similarly, dayPH is being calculated across the entire dataset instead of summing the daily values.

Instead of recalculating the logic at the total level, you might want to wrap the measure in a SUMX over the dates or a virtual table of days to aggregate each day’s result correctly.

Here’s a revised version of your Packing Hours measure that fixes the grand total issue by calculating the packing hours per day and then summing them using SUMX.

Packing Hours =
SUMX (
VALUES ( data_Bins[Date Dumped] ),
VAR currentDate = data_Bins[Date Dumped]

VAR dayPH =
CALCULATE (
DATEDIFF (
MIN ( data_Bins[DateTime Dumped] ),
MAX ( data_Bins[DateTime Dumped] ),
MINUTE
) / 60,
data_Bins[Shift] = "1",
data_Bins[Date Dumped] = currentDate
)

VAR nightStart =
CALCULATE (
MIN ( data_Bins[DateTime Dumped] ),
data_Bins[Date Dumped] = currentDate,
DATEDIFF ( TIME ( 0, 0, 0 ), data_Bins[Time Dumped], HOUR ) > 12
)

VAR nightEndDate = currentDate + 1

VAR nightStop =
CALCULATE (
MAX ( data_Bins[DateTime Dumped] ),
data_Bins[Date Dumped] = nightEndDate,
DATEDIFF ( TIME ( 0, 0, 0 ), data_Bins[Time Dumped], HOUR ) < 3
)

VAR nightPH =
DATEDIFF ( nightStart, nightStop, MINUTE ) / 60

RETURN
SWITCH (
TRUE(),
ISINSCOPE ( ref_Shifts[Short Name] ) && SELECTEDVALUE ( ref_Shifts[Short Name] ) = "Day", dayPH,
ISINSCOPE ( ref_Shifts[Short Name] ) && SELECTEDVALUE ( ref_Shifts[Short Name] ) = "Night", nightPH,
dayPH + nightPH
)
)

This measure uses SUMX(VALUES(data_Bins[Date Dumped]), …) to loop over each date. Within the loop, it calculates dayPH and nightPH per day. Also here it handles both shift-level and total-level correctly using SWITCH(TRUE(), …) logic.

I would also take a moment to thank @Ashish_Excel, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.

[Adams_Apple]

Thank you so much for this, it gets the total correct. however now I am seeing a new issue. For some reason the nights are no longer showing hours. I've narrowed it down to the Var NightStop portion. 
MAX ( data_Bins[DateTime Dumped] ) is pulling in a dateTime.
data_Bins[Date Dumped] = nightEndDate is pulling in nightEndDate correctly.
DATEDIFF ( TIME ( 0, 0, 0 ), data_Bins[Time Dumped], HOUR ) < 3 is where it seems to encounter the issue. It appears a blank value always results.

All i really need to do is determine the hours of the night shift which runs anywhere from 4pm to 3am on the next day. Any ideas? Thank you so much

VAR nightStart =

CALCULATE (

MIN ( data_Bins[DateTime Dumped] ),

data_Bins[Date Dumped] = currentDate,

DATEDIFF ( TIME ( 0, 0, 0 ), data_Bins[Time Dumped], HOUR ) > 12

)

VAR nightEndDate = currentDate + 1

VAR nightStop =

CALCULATE (

MAX ( data_Bins[DateTime Dumped] ),

data_Bins[Date Dumped] = nightEndDate,

DATEDIFF ( TIME ( 0, 0, 0 ), data_Bins[Time Dumped], HOUR ) < 3

)

VAR nightPH =

DATEDIFF ( nightStart, nightStop, MINUTE ) / 60

[v-mdharahman · Accepted solution]

Hi @Adams_Apple,

Thank you for following up with the conversation. You are right that the issue lies in the time part of the logic "DATEDIFF ( TIME(0,0,0), data_Bins[Time Dumped], HOUR ) < 3". The intention here is to filter for times after midnight but before 3:00 AM on the next day of the night shift. However, this condition can be a bit fragile especially since DATEDIFF() with a static TIME(0,0,0) isn't reliable in all row contexts and can sometimes return blanks or unexpected values.

Instead we can use the TIMEVALUE() function directly to filter by the time portion. Since your night shift runs from 4:00 PM to 3:00 AM the next day, you can adjust the logic to use ">= TIME(16, 0, 0) (4:00 PM) to end of day on currentDate" and "< TIME(3, 0, 0) (3:00 AM) on currentDate + 1".

Try this:

VAR nightStart =
CALCULATE(
MIN(data_Bins[DateTime Dumped]),
data_Bins[Date Dumped] = currentDate,
TIMEVALUE(data_Bins[DateTime Dumped]) >= TIME(16, 0, 0)
)

VAR nightStop =
CALCULATE(
MAX(data_Bins[DateTime Dumped]),
data_Bins[Date Dumped] = currentDate + 1,
TIMEVALUE(data_Bins[DateTime Dumped]) < TIME(3, 0, 0)
)

This approach compares the actual time portion of DateTime Dumped directly, which avoids the DATEDIFF ambiguity and handles filtering more reliably.

Best Regards,.

Hammad.

[v-mdharahman]

Hi @Adams_Apple,

As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you need further help.

If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.

If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.
Thank you for your patience and look forward to hearing from you.

[v-mdharahman]

Hi @Adams_Apple,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution so that other community members can find it easily.

Thank you.

[v-mdharahman]

Hi @Adams_Apple,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[Ashish_Excel]

Hi,

Share the download link of the PBI file.  Show the problem and expected result very clearly.

[Adams_Apple]

thank you for replying. unfortunately to PBIX is full of proprietary information.
I am simply trying to get the sum total to add up correctly: