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