Total is not adding up correctly

[AA622]

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 difference the cycle time is 2. I am having trouble getting the total to show the actual cycle time amount. Based on the values for this particular item it should be 22 but i am getting 211. I have no idea where that is coming from. There are only 19 rows for this 1 item. 15 of those rows show a value and 4 do not. Either way it comes out to 22 but it is showing 211.

This is the measure I am using to count cycle time. I think the discrepancy is coming from the empty rows, just a hunch.

Prod_CycleTime_Workday = SUMx(

    FILTER (

        'Calendar',

        'Calendar'[Date] >= MIN( 'Cycle Time'[Production_Start date] )

            &&

        'Calendar'[Date] <= MAX( 'Cycle Time'[Production_End date] )

    ),

    'Calendar'[IsWorkdayNoHoliday]

)

Is something wrong with the measure that is getting me 211?

[ImkeF · Accepted solution]

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]

Hi @AA622 ,
you are probably iterating the wrong table here: At the total-level, there will be only one value for the MIN( 'Cycle Time'[Production_Start date] ) and MAX( 'Cycle Time'[Production_Start date] ). Those will be the earliest and latest dates for ALL start dates in your CycleTime-table. This will basically remove all filters on the iterated items and cause the number to be that high.

Instead, you should iterate a table that contains the ItemID and ProductionsID-fields. (Hard to be more specific here without seeing the details of your data model).
Then, make sure to use a CALCULATE around the 'Calendar'[IsWorkdayNoHoliday] , to cover the effects of the row context within iterators: CALCULATE('Calendar'[IsWorkdayNoHoliday]) 

[AA622]

Hello

What more details do you need? All the fields for this particular report are housed within the same table from a SQL query join. Item ID and productionID are in the same table. You can call it Cycle Time if that helps.

I understand that it needs to iterate from the ItemId/Production ID but I am having trouble structuring the new measure. Any insight?