Forum Discussion
Another Matrix Sum Issue
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.
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.
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
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.
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.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.