Running Totals

[e175429]

Hello All,

This is a sample of my data:

Anyone know what I should do to get the running totals for the Users?

Mind you, my actual data is over 43,000 rows.

I tried to upload a sample file but you have to be a SuperUser.

[techies]

Hi @e175429 please try this

Running Total =

CALCULATE (

    COUNTROWS(payments),

    FILTER (

        ALLEXCEPT ( payments, payments[User] ),  

        payments[PaidDate]<= MAX ( payments[PaidDate] )

    )

)

[djurecicK2]

Hi @e175429 ,

 In addition to what @Rupak_bi has said, you might look at using the RUNNINGSUM visual calculation. Here is some additional info:

https://www.youtube.com/watch?v=pqfBH0ZmYkA

[Rupak_bi]

hi @e175429 ,

Please share the sample data in plain text (currently ist an image and cant copy). will try to buiid the solution.

[e175429]

@Rupak_bi 

A very samall portion of my actal data:

Date:                  User:

7/1/2023            Nervis

7/1/2023            Nervis

7/1/2023            Clay

7/3/2023            Walter

7/3/2023            Leveston

7/3/2023            Leveston

7/3/2023            Nervis

7/5/2023            Clay

7/9/2023            Clay

8/14/2023          Walter

8/14/2023          Leveston

8/14/2023          Nervis

2/27/2024          Clay

2/27/2024          Leveston

2/27/2024          Nervis

2/27/2024          Walter

2/27/2024          Leveston

2/27/2024          Leveston

3/19/2024          Clay

[Shahid12523]

Power BI (DAX):

Running Total =
CALCULATE(
COUNTROWS('Table'),
FILTER(
ALLSELECTED('Table'),
'Table'[User] = MAX('Table'[User]) &&
'Table'[PaidDate] <= MAX('Table'[PaidDate])
)
)

SQL:

COUNT(*) OVER (PARTITION BY [User] ORDER BY PaidDate) AS RunningTotal

[Sahir_Maharaj]

Hello @e175429,

Could you please try the following approach:

Running Total per User :=
VAR CurrentDate = MAX ( 'Fact'[PaidDate] )
RETURN
CALCULATE (
    COUNTROWS ( 'Fact' ),
    FILTER (
        ALLSELECTED ( 'Fact'[PaidDate] ),
        'Fact'[PaidDate] <= CurrentDate
    )
)

[e175429]

Hey,

Thank you for your response. I've created the measure but I'm not sure how to graph this.

This is what I get:

Which is not exactly what I am looking for. 

In the table, I want the running totals for everyone, and then when I put a filter on, I would like the running total for that one individual. 

I have already created a date table:

[MasonMA]

Hello @e175429 

1. Build a Date table if this doesn't exist. Suppose this Date table is called 'Date'. 

2. Suppose the table that has data on your screenshot is called 'Fact'. Create a proper one-to-many relationship from 'Date' to your 'Fact'[PaidDate] 

You can use measure, 

RunningTotal:=
CALCULATE(
COUNTROWS('Fact'),
FILTER(
ALL('Date'[Date]), 
'Date'[Date] <= MAX('Date'[Date])
),
VALUES('Fact'[User])
)

Thanks. 

[e175429]

Hey,

Thank you for your response, but I'm still having a issue.

So, I created your measure and got this:

Which is great!

What would I have to do to get a column that shows the total by month per user?

For instance, under Staley, a column next to "running total" would be month "monthly total" where

Sept-24 = 2

Aug-24 = 2

Dec-23 = 5

Oct-23  = 1