Forum Discussion

uscutieda's avatar
uscutieda
Frequent Visitor
6 years ago
Solved

Calculate Production Rate per hour

Hello all I need some help to calculate the rate per hours in a PBI table, using the column "Time" as a reference.  This is what I have so far in PBI An this is what I'd like to have: (I bui...
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    hi uscutieda 

    The problem is that your [Date] column is a datetime format, and they have different time value in each day, so they are different value for each day.

    You just need to add a date format column for [Date], and then use this date format column in the formula.

    New Date = DATE(YEAR('AL2 CP TotalCount'[Date]),MONTH('AL2 CP TotalCount'[Date]),DAY('AL2 CP TotalCount'[Date]))
    Result = 
    VAR _lastlinetime = 
        CALCULATE(
            MAX('AL2 CP TotalCount'[Time]),
            FILTER(
                'AL2 CP TotalCount',
                'AL2 CP TotalCount'[Line] = EARLIER('AL2 CP TotalCount'[Line])
                    && 'AL2 CP TotalCount'[New Date] = EARLIER('AL2 CP TotalCount'[New Date])
                    && 'AL2 CP TotalCount'[Time] < EARLIER('AL2 CP TotalCount'[Time])
            )
        )
    RETURN
        VAR _lastlinevalue =
            IF(
               ISBLANK(_lastlinetime),
               0,
               CALCULATE(
                   SUM('AL2 CP TotalCount'[Value]),
                   FILTER(
                       'AL2 CP TotalCount',
                       'AL2 CP TotalCount'[Line] = EARLIER('AL2 CP TotalCount'[Line])
                           && 'AL2 CP TotalCount'[New Date] = EARLIER('AL2 CP TotalCount'[New Date])
                           && 'AL2 CP TotalCount'[Time] = _lastlinetime
                    )
                )
            )
        RETURN
            'AL2 CP TotalCount'[Value] - _lastlinevalue

    and here is sample pbix file, please try it.

     

    Regards,

    Lin