Forum Discussion

alex9999's avatar
alex9999
Icon for Helper I rankHelper I
4 years ago
Solved

Cumulative Total by hour

I've checked most of the forum posts regarding cumulative and running totals but none of them seem to be working with my report. 

 

DateIn/OutTonnage
1/04/2022In345
1/04/2022Out335
10/04/2022In355
11/04/2022In20

 

I currently have a line graph with the date on the X-Axis, and the tonnage on the Y-Axis. I want the tonnage to be running through cumunitavely (in for counting for plus tonnage, out counting for minus tonnage). 

 

For example, 1/04/2022 will read as 10 on Y-Axis (345 - 335).

 

10/04/2022 will read as 365 on Y-Axis (10 + 355)

 

11/04/2022 will read as 385 on Y-Axis (365 + 20)

 

Any help will be appreciated 🙂

  • Fowmy's avatar
    Fowmy
    4 years ago

    alex9999 

    Please check the attached file:

     

    Running Total = 
VAR __Date = max( Dates[Date])
VAR __InTotal = 
    CALCULATE(
        SUM(Table1[Tonnage]),
        Table1[In/Out] = "IN",
        'Dates'[Date] <= __Date,        
        ALLSELECTED( 'Dates' )        
    )
VAR __OutTotal = 
    CALCULATE(
        SUM(Table1[Tonnage]),
        Table1[In/Out] = "OUT",
        'Dates'[Date] <= __Date,        
        ALLSELECTED( 'Dates' )        
    )
RETURN
   IF( sum(Table1[Tonnage]) <> BLANK(),
       __InTotal - __OutTotal
    )

     



2 Replies

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    4 years ago

    alex9999 

    Please check the attached file:

     

    Running Total = 
VAR __Date = max( Dates[Date])
VAR __InTotal = 
    CALCULATE(
        SUM(Table1[Tonnage]),
        Table1[In/Out] = "IN",
        'Dates'[Date] <= __Date,        
        ALLSELECTED( 'Dates' )        
    )
VAR __OutTotal = 
    CALCULATE(
        SUM(Table1[Tonnage]),
        Table1[In/Out] = "OUT",
        'Dates'[Date] <= __Date,        
        ALLSELECTED( 'Dates' )        
    )
RETURN
   IF( sum(Table1[Tonnage]) <> BLANK(),
       __InTotal - __OutTotal
    )

     



    • alex9999's avatar
      alex9999
      Icon for Helper I rankHelper I
      4 years ago

      Thank you, exactly what I was looking for! Great help 🙂