Forum Discussion

Ibad_Khan's avatar
Ibad_Khan
Helper II
4 years ago
Solved

Sum the Max Value before 0

Hello PBI Community,

Hope you all are doing good.

 

I want to sort my data based of DateTime column and the perform the sum of max value before zero. Actually I want to find Total production and counter of production is incremented and count of previous production is added in new count of production. When the shift changes or machine stops due to any reason, counter is reset to 0. I want to find shift wise production. Below is the sample data for reference and want to sum the Red Highlighted Bold Values based on scenario.

 

TimeShiftProduction
7:00:00 AMShift A0
7:02:00 AMShift A5
7:04:00 AMShift A10
7:06:00 AMShift A15
7:08:00 AMShift A0
7:10:00 AMShift A5
7:12:00 AMShift A10
7:14:00 AMShift A15
7:16:00 AMShift A20
7:18:00 AMShift A25
7:20:00 AMShift A30
7:22:00 AMShift B0
7:24:00 AMShift B5
7:26:00 AMShift B10
7:28:00 AMShift B15
7:30:00 AMShift B0
7:32:00 AMShift B5
7:34:00 AMShift B10
7:36:00 AMShift B15
7:38:00 AMShift B20
7:40:00 AMShift B0

 

Result I want is,

Shift A = 15 + 30 = 45

Shift B = 15 + 20 = 35

 

Looking forward for some postive responce.

 

Regards,

Ibad.

 

amitchandak  DataInsights v-zhangti  ribisht17 

Need Help
Show and Tell
Tips and Tricks
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    Please check the attached pbix file.

    Regarding 9/4/2022 shiftB, I am not sure but I added the last production number (2) and the number shows 18.

     

    desired outcome measure V2: =
VAR newtable =
    ADDCOLUMNS (
        ADDCOLUMNS (
            Data_Second,
            "@previousvalue",
                VAR _currentdate = Data_Second[Date]
                VAR _currenttime = Data_Second[Time]
                VAR _currentshift = Data_Second[Shift]
                VAR _previoustime =
                    MAXX (
                        FILTER (
                            Data_Second,
                            Data_Second[Shift] = _currentshift
                                && Data_Second[Date] = _currentdate
                                && Data_Second[Time] < _currenttime
                        ),
                        Data_Second[Time]
                    )
                VAR _previousvalue =
                    MAXX (
                        FILTER (
                            Data_Second,
                            Data_Second[Shift] = _currentshift
                                && Data_Second[Date] = _currentdate
                                && Data_Second[Time] = _previoustime
                        ),
                        Data_Second[Production]
                    )
                RETURN
                    _previousvalue
        ),
        "@index",
            IF (
                [@previousvalue]
                    == BLANK ()
                        || Data_Second[Production] < [@previousvalue],
                1,
                0
            )
    )
VAR indexcumulatetable =
    ADDCOLUMNS (
        newtable,
        "@indexcumulate",
            SUMX (
                FILTER ( newtable, Data_Second[Time] <= EARLIER ( Data_Second[Time] ) ),
                [@index]
            )
    )
VAR groupbyindexcumulatemax =
    SUMMARIZE (
        GROUPBY (
            indexcumulatetable,
            Shift[Shift],
            [@indexcumulate],
            "@maxtime", MAXX ( CURRENTGROUP (), Data_Second[Time] )
        ),
        [@maxtime]
    )
RETURN
    IF (
        HASONEVALUE ( Shift[Shift] ),
        SUMX (
            FILTER ( Data_Second, Data_Second[Time] IN groupbyindexcumulatemax ),
            Data_Second[Production]
        )
    )

6 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Ibad_Khan , create a new column

     


    Column = var _max = Minx(filter(Data, [Shift] = EARLIER([Shift]) && [Time] > EARLIER([Time])), [Time])
    Var _val = Minx(filter(Data, [Shift] = EARLIER([Shift]) && [Time] =_max ), [Production])
    return if(_val =0 , [Production], BLANK())

     

     

    You can use this in measure now

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

    Hi,

    Please check the below picturea and the attached pbix file.

     

     

    desired outcome measure: =
VAR newtable =
    ADDCOLUMNS ( Data, "@index", IF ( Data[Production] = 0, 1, 0 ) )
VAR indexcumulatetable =
    ADDCOLUMNS (
        newtable,
        "@indexcumulate", SUMX ( FILTER ( newtable, Data[Time] <= EARLIER ( Data[Time] ) ), [@index] )
    )
VAR groupbyindexcumulatemax =
    SUMMARIZE (
        GROUPBY (
            indexcumulatetable,
            Shift[Shift],
            [@indexcumulate],
            "@maxtime", MAXX ( CURRENTGROUP (), Data[Time] )
        ),
        [@maxtime]
    )
RETURN
    SUMX (
        FILTER ( data, Data[Time] IN groupbyindexcumulatemax ),
        Data[Production]
    )
    • Ibad_Khan's avatar
      Ibad_Khan
      Helper II
      4 years ago

      Hey Jihwan_Kim  thanks alot for your support and quick responce. It's working corrctly according to given condition, but I got an other scenario that if the next value is less than current value (not specifically 0) than consider current value as the Max of counter for calculating production.

      Below is the sample data for your understanding,

       

      DateTimeShiftProduction
      8/4/20227:04:00 AMShift A10
      8/4/20227:06:00 AMShift A15
      8/4/20227:08:00 AMShift A0
      8/4/20227:10:00 AMShift A5
      8/4/20227:12:00 AMShift A10
      8/4/20227:14:00 AMShift A15
      8/4/20227:16:00 AMShift A20
      8/4/20227:18:00 AMShift A25
      8/4/20227:20:00 AMShift A30
      8/4/20227:22:00 AMShift B5
      8/4/20227:24:00 AMShift B10
      8/4/20227:26:00 AMShift B15
      8/4/20227:28:00 AMShift B20
      8/4/20227:30:00 AMShift B4
      8/4/20227:32:00 AMShift B5
      8/4/20227:34:00 AMShift B10
      8/4/20227:36:00 AMShift B15
      8/4/20227:38:00 AMShift B20
      8/4/20227:40:00 AMShift B0
      9/4/20227:00:00 AMShift A2
      9/4/20227:02:00 AMShift A4
      9/4/20227:04:00 AMShift A6
      9/4/20227:06:00 AMShift A2
      9/4/20227:08:00 AMShift A4
      9/4/20227:10:00 AMShift A1
      9/4/20227:12:00 AMShift A4
      9/4/20227:14:00 AMShift A6
      9/4/20227:16:00 AMShift A8
      9/4/20227:18:00 AMShift A10
      9/4/20227:20:00 AMShift A12
      9/4/20227:22:00 AMShift B0
      9/4/20227:24:00 AMShift B2
      9/4/20227:26:00 AMShift B4
      9/4/20227:28:00 AMShift B6
      9/4/20227:30:00 AMShift B0
      9/4/20227:32:00 AMShift B4
      9/4/20227:34:00 AMShift B6
      9/4/20227:36:00 AMShift B8
      9/4/20227:38:00 AMShift B10
      9/4/20227:40:00 AMShift B2

       

      Desired output is:

      8/4/2022Shift A45
      8/4/2022Shift B40
      9/4/2022Shift A22
      9/4/2022Shift B16

       

      Looking forward for your responce.

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Super User
        4 years ago

        Hi,

        Please check the attached pbix file.

        Regarding 9/4/2022 shiftB, I am not sure but I added the last production number (2) and the number shows 18.

         

        desired outcome measure V2: =
VAR newtable =
    ADDCOLUMNS (
        ADDCOLUMNS (
            Data_Second,
            "@previousvalue",
                VAR _currentdate = Data_Second[Date]
                VAR _currenttime = Data_Second[Time]
                VAR _currentshift = Data_Second[Shift]
                VAR _previoustime =
                    MAXX (
                        FILTER (
                            Data_Second,
                            Data_Second[Shift] = _currentshift
                                && Data_Second[Date] = _currentdate
                                && Data_Second[Time] < _currenttime
                        ),
                        Data_Second[Time]
                    )
                VAR _previousvalue =
                    MAXX (
                        FILTER (
                            Data_Second,
                            Data_Second[Shift] = _currentshift
                                && Data_Second[Date] = _currentdate
                                && Data_Second[Time] = _previoustime
                        ),
                        Data_Second[Production]
                    )
                RETURN
                    _previousvalue
        ),
        "@index",
            IF (
                [@previousvalue]
                    == BLANK ()
                        || Data_Second[Production] < [@previousvalue],
                1,
                0
            )
    )
VAR indexcumulatetable =
    ADDCOLUMNS (
        newtable,
        "@indexcumulate",
            SUMX (
                FILTER ( newtable, Data_Second[Time] <= EARLIER ( Data_Second[Time] ) ),
                [@index]
            )
    )
VAR groupbyindexcumulatemax =
    SUMMARIZE (
        GROUPBY (
            indexcumulatetable,
            Shift[Shift],
            [@indexcumulate],
            "@maxtime", MAXX ( CURRENTGROUP (), Data_Second[Time] )
        ),
        [@maxtime]
    )
RETURN
    IF (
        HASONEVALUE ( Shift[Shift] ),
        SUMX (
            FILTER ( Data_Second, Data_Second[Time] IN groupbyindexcumulatemax ),
            Data_Second[Production]
        )
    )