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
Icon for Community Support rankCommunity 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
    Icon for Community Support rankCommunity 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

    • uscutieda's avatar
      uscutieda
      Frequent Visitor
      6 years ago

      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.