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: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
jc173
Frequent Visitor

Running total with different column condition

‎05-13-2024 07:53 AM

Hi guys i have this data :

TIMESTAMP_INIZIO_ARRTIMESTAMP_FINE_ARRINSMANTURNOvalue
2024-05-04 05:30:00.0002024-05-04 06:00:00.00011926
2024-05-04 06:00:00.0002024-05-04 06:30:00.000112305
2024-05-04 06:30:00.0002024-05-04 07:00:00.000110
2024-05-04 07:00:00.0002024-05-04 07:30:00.000112214
2024-05-04 07:30:00.0002024-05-04 08:00:00.000112779
2024-05-04 08:00:00.0002024-05-04 08:30:00.000113163
2024-05-04 08:30:00.0002024-05-04 09:00:00.000112960
2024-05-04 09:00:00.0002024-05-04 09:30:00.000113141
2024-05-04 09:30:00.0002024-05-04 10:00:00.000113141
2024-05-04 10:00:00.0002024-05-04 10:30:00.000112983
2024-05-04 10:30:00.0002024-05-04 11:00:00.000111649
2024-05-04 11:00:00.0002024-05-04 11:30:00.000113141
2024-05-04 11:30:00.0002024-05-04 12:00:00.000112937
2024-05-04 12:00:00.0002024-05-04 12:30:00.000111717
2024-05-04 12:30:00.0002024-05-04 13:00:00.000110
2024-05-04 13:00:00.0002024-05-04 13:30:00.000111175
2024-05-04 13:30:00.0002024-05-04 14:00:00.000123163
2024-05-04 14:00:00.0002024-05-04 14:30:00.000123118
2024-05-04 14:30:00.0002024-05-04 15:00:00.000122983
2024-05-04 15:00:00.0002024-05-04 15:30:00.000123141
2024-05-04 15:30:00.0002024-05-04 16:00:00.000122983
2024-05-04 16:00:00.0002024-05-04 16:30:00.000123005
2024-05-04 16:30:00.0002024-05-04 17:00:00.000123412
2024-05-04 17:00:00.0002024-05-04 17:30:00.000123141
2024-05-04 17:30:00.0002024-05-04 18:00:00.000122757
2024-05-04 18:00:00.0002024-05-04 18:30:00.000123163
2024-05-04 18:30:00.0002024-05-04 19:00:00.000122960
2024-05-04 19:00:00.0002024-05-04 19:30:00.000122779
2024-05-04 19:30:00.0002024-05-04 20:00:00.000133163
2024-05-04 20:00:00.0002024-05-04 20:30:00.000131853
2024-05-04 20:30:00.0002024-05-04 21:00:00.000130
2024-05-04 21:00:00.0002024-05-04 21:30:00.000130
2024-05-04 21:30:00.0002024-05-04 22:00:00.00013429
2024-05-04 22:00:00.0002024-05-04 22:30:00.000131898
2024-05-04 22:30:00.0002024-05-04 23:00:00.000133163
2024-05-04 23:00:00.0002024-05-04 23:30:00.000133118
2024-05-04 23:30:00.0002024-05-05 00:00:00.000133163
2024-05-05 00:00:00.0002024-05-05 00:30:00.000132824
2024-05-05 00:30:00.0002024-05-05 01:00:00.000133141
2024-05-05 01:00:00.0002024-05-05 01:30:00.000132870
2024-05-05 01:30:00.0002024-05-05 02:00:00.000132485
2024-05-05 02:00:00.0002024-05-05 02:30:00.00013926
2024-05-05 02:30:00.0002024-05-05 03:00:00.000132305
2024-05-05 03:00:00.0002024-05-05 03:30:00.000130
2024-05-05 03:30:00.0002024-05-05 04:00:00.000132214
2024-05-05 04:00:00.0002024-05-05 04:30:00.000132779
2024-05-05 04:30:00.0002024-05-05 05:00:00.000133163
2024-05-05 05:00:00.0002024-05-05 05:30:00.000132960
2024-05-05 05:30:00.0002024-05-05 06:00:00.000113141
2024-05-05 06:00:00.0002024-05-05 06:30:00.000113141
2024-05-05 06:30:00.0002024-05-05 07:00:00.000112983
2024-05-05 07:00:00.0002024-05-05 07:30:00.000111649
2024-05-05 07:30:00.0002024-05-05 08:00:00.000113141
2024-05-05 08:00:00.0002024-05-05 08:30:00.000112937
2024-05-05 08:30:00.0002024-05-05 09:00:00.000111717
2024-05-05 09:00:00.0002024-05-05 09:30:00.000110
2024-05-05 09:30:00.0002024-05-05 10:00:00.000111175
2024-05-05 10:00:00.0002024-05-05 10:30:00.000113163
2024-05-05 10:30:00.0002024-05-05 11:00:00.000113118
2024-05-05 11:00:00.0002024-05-05 11:30:00.000112983
2024-05-05 11:30:00.0002024-05-05 12:00:00.000113141
2024-05-05 12:00:00.0002024-05-05 12:30:00.000112983
2024-05-05 12:30:00.0002024-05-05 13:00:00.000113005
2024-05-05 13:00:00.0002024-05-05 13:30:00.000113412

i have to calculate the cumulative sum based on the "turno " column , so every time the shift changes i have to reset the cumulative sum to zero and restart, i also have the day as a parameter, but the column turno changes even betweem one day and another, i honestly don't know how to do it

Labels:
Message 1 of 3
58 Views
0
1 ACCEPTED SOLUTION
v-yohua-msft
Community Support
Community Support

‎05-14-2024 07:04 PM

Hi, @jc173 

First, you need to create an Index, which can be created by selecting AddColumn in PowerQuery.

vyohuamsft_0-1715738539637.png

 

Apply and close. Then create a calculated column.

Cumulative Sum = 
VAR CurrentTurno = 'Table'[TURNO]
RETURN
    CALCULATE(
        SUM('Table'[value]),
        FILTER(
            ALL('Table'),
            'Table'[Index] <= EARLIER('Table'[Index]) &&
            'Table'[TURNO] = CurrentTurno
        )
    )

 

Here is my preview:

vyohuamsft_1-1715738646043.png

 

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Running total with different column condition.pbix
Message 2 of 3
44 Views
0
2 REPLIES 2
Ashish_Mathur
Super User
Super User

‎05-14-2024 07:55 PM

Hi,

In another column, show the expected result very clearly.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Message 3 of 3
31 Views
0
v-yohua-msft
Community Support
Community Support

‎05-14-2024 07:04 PM

Hi, @jc173 

First, you need to create an Index, which can be created by selecting AddColumn in PowerQuery.

vyohuamsft_0-1715738539637.png

 

Apply and close. Then create a calculated column.

Cumulative Sum = 
VAR CurrentTurno = 'Table'[TURNO]
RETURN
    CALCULATE(
        SUM('Table'[value]),
        FILTER(
            ALL('Table'),
            'Table'[Index] <= EARLIER('Table'[Index]) &&
            'Table'[TURNO] = CurrentTurno
        )
    )

 

Here is my preview:

vyohuamsft_1-1715738646043.png

 

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Running total with different column condition.pbix
Message 2 of 3
45 Views
0

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel1

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

View All