Calculate Production Rate per hour

[uscutieda]

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 built it in Excel 😏 )

Thank you all in advance

[Ashish_Mathur]

Hi,

Assuming the numbers in the value column will always be in ascending order, try this calculated column formula

=[Value]-CALCULATE(MAX(Data[Value]),FILTER(Data,Data[Time]<EARLIER(Data[Time])&&Data[Line]=EARLIER(Data[Line]))

Hope this helps.

[v-lili6-msft]

hi @uscutieda 

Just use this formula to create a column as below:

Result =
VAR _lastlinetime =
    CALCULATE (
        MAX ( 'Table'[Time] ),
        FILTER (
            'Table',
            'Table'[Line] = EARLIER ( 'Table'[Line] )
                && 'Table'[Date] = EARLIER ( 'Table'[Date] )
                && 'Table'[Time] < EARLIER ( 'Table'[Time] )
        )
    )
RETURN
    VAR _lastlinevalue =
        IF (
            ISBLANK ( _lastlinetime ),
            0,
            CALCULATE (
                SUM ( 'Table'[Value] ),
                FILTER (
                    'Table',
                    'Table'[Line] = EARLIER ( 'Table'[Line] )
                        && 'Table'[Date] = EARLIER ( 'Table'[Date] )
                        && 'Table'[Time] = _lastlinetime
                )
            )
        )
    RETURN
        'Table'[Value] - _lastlinevalue

Result:

and here is sample pbix file, please try it.

Regards,

Lin

[uscutieda]

@v-lili6-msft

When I use the code provided in your example, it works, but when I use it in my pbix file I get the same number of my "Value"column because _lastlinevalue is always 0. I checked all the column format to match yours but still didn't work.

Here is how I applied your solution to my file. Do you see enything wrong? Attached is a pbix file with a sample of my actual table

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'[Date] = EARLIER('AL2 CP TotalCount'[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'[Date] = EARLIER('AL2 CP TotalCount'[Date])
                       && 'AL2 CP TotalCount'[Time] = _lastlinetime
                )
            )
        )
    RETURN
        'AL2 CP TotalCount'[Value] - _lastlinevalue

This is what I get:

Here is the link to the pbx file

[v-lili6-msft · Accepted solution]

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

[uscutieda]

Thanks to everyone that collaborated especially to @v-lili6-msft Thanks again for all your help

One last question 🤔 😁

The calculated column is working as it should, but the issue that I'm experiencing now is when the date change to the next day, my calculated column (Count) is not subtracting the value from the previous last hour of the day. What modification to the formula you recommend to overcome this issue? I tried changing the "Time" column format to "hh:mm tt" but didn't work

[v-lili6-msft]

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

[uscutieda]

@v-lili6-msft I do need to calculate it by time, I just need the formula to work for the first hour of the day as well.

[parry2k]

@uscutieda can you upload the images again, it is not available.

[uscutieda]

@parry2k Thanks for the heads up, images are visible now