Forum Discussion

AA622's avatar
AA622
Helper II
3 years ago
Solved

Total is not adding up correctly

Hello   I am trying to calculate total batch time. Please refer to the image below.   The column named Cycle time shows the amount of days between start date + end date. Even if its 1 day d...
Need Help
Tips and Tricks
  • ImkeF's avatar
    ImkeF
    3 years ago

    Hi AA622 ,
    a possible solution could look like so:

    Measure = 
SUMX (
    SUMMARIZE ( 'Table', 'Table'[ItemID], 'Table'[ProductionID] ),
    VAR _StartTime =
        CALCULATE ( MIN ( 'Table'[StartTime] ) )
    VAR _EndTime =
        CALCULATE ( MAX ( 'Table'[EndTime] ) )
    RETURN
        CALCULATE (
            SUM ( 'Calendar'[IsWorkdayNoHoliday] ),
            'Calendar'[Date] >= _StartTime,
            'Calendar'[Date] <= _EndTime
        )
)

    Next time, please provide sample data in a usable form.
    Please also see file attached.

ImkeF's avatar
ImkeF
Community Champion
3 years ago

Hi AA622 ,
a possible solution could look like so:

Measure = 
SUMX (
    SUMMARIZE ( 'Table', 'Table'[ItemID], 'Table'[ProductionID] ),
    VAR _StartTime =
        CALCULATE ( MIN ( 'Table'[StartTime] ) )
    VAR _EndTime =
        CALCULATE ( MAX ( 'Table'[EndTime] ) )
    RETURN
        CALCULATE (
            SUM ( 'Calendar'[IsWorkdayNoHoliday] ),
            'Calendar'[Date] >= _StartTime,
            'Calendar'[Date] <= _EndTime
        )
)

Next time, please provide sample data in a usable form.
Please also see file attached.