Another Matrix Sum Issue

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)

Need Help

Anonymous
1 year ago
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.

8 Replies

Ashish_Excel
Solution Supplier
1 year ago
Hi,
Share the download link of the PBI file. Show the problem and expected result very clearly.
- Adams_Apple
  Frequent Visitor
  1 year ago
  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:
Anonymous
Not applicable
1 year ago
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
  Frequent Visitor
  1 year ago
  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
  - Anonymous
    Not applicable
    1 year ago
    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.

Forum Discussion

Another Matrix Sum Issue

8 Replies

Related content

Matrix Visual Sorting Issue

Matrix calculation Issue - DAX

Matrix visual issue !!

Matrix alignment issue

Matrix Totals & Visual issue