Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
Ibad_Khan
Helper II
Helper II

Sum the Max Value before 0

‎04-13-2022 11:19 PM

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 

Message 1 of 7
542 Views
0
1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User
In response to Ibad_Khan

‎04-14-2022 01:35 AM

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

 

    Microsoft MVP
 

 

   


      If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


   


     
        LinkedInVisit my LinkedIn page
     
   


   


     
        Outlook BookingSchedule a short Teams meeting to discuss your question

     
   


 


View solution in original post

lbadkhan.pbix
Message 6 of 7
457 Views
0
6 REPLIES 6
Jihwan_Kim
Super User
Super User

‎04-14-2022 12:09 AM

Hi,

Please check the below picturea and the attached pbix file.

 

Picture1.png

 

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

 

    Microsoft MVP
 

 

   


      If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


   


     
        LinkedInVisit my LinkedIn page
     
   


   


     
        Outlook BookingSchedule a short Teams meeting to discuss your question

     
   


 


lbadkhan.pbix
Message 4 of 7
484 Views
0
Ibad_Khan
Helper II
Helper II
In response to Jihwan_Kim

‎04-14-2022 12:44 AM

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.

Message 5 of 7
478 Views
0
Jihwan_Kim
Super User
Super User
In response to Ibad_Khan

‎04-14-2022 01:35 AM

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

 

    Microsoft MVP
 

 

   


      If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


   


     
        LinkedInVisit my LinkedIn page
     
   


   


     
        Outlook BookingSchedule a short Teams meeting to discuss your question

     
   


 


lbadkhan.pbix
Message 6 of 7
458 Views
0
Ibad_Khan
Helper II
Helper II
In response to Jihwan_Kim

‎04-14-2022 09:13 PM

@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

Message 7 of 7
448 Views
0
amitchandak
Super User
Super User

‎04-13-2022 11:57 PM

@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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Message 2 of 7
490 Views
0
Ibad_Khan
Helper II
Helper II
In response to amitchandak

‎04-14-2022 12:05 AM

@amitchandak  it's not calculating properly.

Message 3 of 7
484 Views
0

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

May 2025 Monthly Update

Fabric Community Update - May 2025

Find out what's new and trending in the Fabric community.

View All