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

Thank you all in advance

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

9 Replies

  • 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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    6 years ago

    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.