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...

Need Help

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

Calculate Production Rate per hour with my Table.pbix104 KB

v-lili6-msft

Community Support

6 years ago

hi uscutieda

If you don't need to calculate it only by day, you could use this formula:

Column = 
VAR _lastlinetime = 
    CALCULATE(
        MAX('AL2 CP TotalCount'[Date]),
        FILTER(
            'AL2 CP TotalCount',
            'AL2 CP TotalCount'[Line] = EARLIER('AL2 CP TotalCount'[Line])
                && 'AL2 CP TotalCount'[Date] < EARLIER('AL2 CP TotalCount'[Date])
        )
    )
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'[Date] = _lastlinetime
                )
            )
        )
    RETURN
        'AL2 CP TotalCount'[Value] - _lastlinevalue

Regards,

Lin