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...
Need Help
  • 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

v-lili6-msft's avatar
v-lili6-msft
Community Support
6 years ago

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's avatar
uscutieda
Frequent Visitor
6 years ago

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's avatar
    v-lili6-msft
    Community Support
    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

    • uscutieda's avatar
      uscutieda
      Frequent Visitor
      6 years ago

      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's avatar
        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