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 c...
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]
        )
    )
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]
        )
    )