Sum the Max Value before 0

[Ibad_Khan]

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.

Time	Shift	Production
7:00:00 AM	Shift A	0
7:02:00 AM	Shift A	5
7:04:00 AM	Shift A	10
7:06:00 AM	Shift A	15
7:08:00 AM	Shift A	0
7:10:00 AM	Shift A	5
7:12:00 AM	Shift A	10
7:14:00 AM	Shift A	15
7:16:00 AM	Shift A	20
7:18:00 AM	Shift A	25
7:20:00 AM	Shift A	30
7:22:00 AM	Shift B	0
7:24:00 AM	Shift B	5
7:26:00 AM	Shift B	10
7:28:00 AM	Shift B	15
7:30:00 AM	Shift B	0
7:32:00 AM	Shift B	5
7:34:00 AM	Shift B	10
7:36:00 AM	Shift B	15
7:38:00 AM	Shift B	20
7:40:00 AM	Shift B	0

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 

[Jihwan_Kim]

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]

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,

Date	Time	Shift	Production
8/4/2022	7:04:00 AM	Shift A	10
8/4/2022	7:06:00 AM	Shift A	15
8/4/2022	7:08:00 AM	Shift A	0
8/4/2022	7:10:00 AM	Shift A	5
8/4/2022	7:12:00 AM	Shift A	10
8/4/2022	7:14:00 AM	Shift A	15
8/4/2022	7:16:00 AM	Shift A	20
8/4/2022	7:18:00 AM	Shift A	25
8/4/2022	7:20:00 AM	Shift A	30
8/4/2022	7:22:00 AM	Shift B	5
8/4/2022	7:24:00 AM	Shift B	10
8/4/2022	7:26:00 AM	Shift B	15
8/4/2022	7:28:00 AM	Shift B	20
8/4/2022	7:30:00 AM	Shift B	4
8/4/2022	7:32:00 AM	Shift B	5
8/4/2022	7:34:00 AM	Shift B	10
8/4/2022	7:36:00 AM	Shift B	15
8/4/2022	7:38:00 AM	Shift B	20
8/4/2022	7:40:00 AM	Shift B	0
9/4/2022	7:00:00 AM	Shift A	2
9/4/2022	7:02:00 AM	Shift A	4
9/4/2022	7:04:00 AM	Shift A	6
9/4/2022	7:06:00 AM	Shift A	2
9/4/2022	7:08:00 AM	Shift A	4
9/4/2022	7:10:00 AM	Shift A	1
9/4/2022	7:12:00 AM	Shift A	4
9/4/2022	7:14:00 AM	Shift A	6
9/4/2022	7:16:00 AM	Shift A	8
9/4/2022	7:18:00 AM	Shift A	10
9/4/2022	7:20:00 AM	Shift A	12
9/4/2022	7:22:00 AM	Shift B	0
9/4/2022	7:24:00 AM	Shift B	2
9/4/2022	7:26:00 AM	Shift B	4
9/4/2022	7:28:00 AM	Shift B	6
9/4/2022	7:30:00 AM	Shift B	0
9/4/2022	7:32:00 AM	Shift B	4
9/4/2022	7:34:00 AM	Shift B	6
9/4/2022	7:36:00 AM	Shift B	8
9/4/2022	7:38:00 AM	Shift B	10
9/4/2022	7:40:00 AM	Shift B	2

Desired output is:

8/4/2022	Shift A	45
8/4/2022	Shift B	40
9/4/2022	Shift A	22
9/4/2022	Shift B	16

Looking forward for your responce.

[Jihwan_Kim · Accepted solution]

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]

@Jihwan_Kim  Thanks alot, it's working perfectly, I'm accepting it as a solution.

I have posted one more issue regarding same dashboard, I'm pasting the link below, can you please check it out and look into it that how can we do it?

https://community.powerbi.com/t5/Desktop/Setting-Shifts-for-analysis-of-machine-data/m-p/2454713#M87...

Looking forward for some positive responce.

Regards,

Ibad

[amitchandak]

@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

[Ibad_Khan]

@amitchandak  it's not calculating properly.