Running MTD Totals per Employee

[ovetteabejuela]

I want to do a running totals per employee but I'm not sure how to modify the code found here (by @Sean😞

Here's a simple table:

Date	Employee	Daily Total Sales	MTD Sales
12/1/2016	12345	53523
12/2/2016	12345	83572
12/3/2016	12345	18274
12/4/2016	12345	35805
12/5/2016	12345	53150
12/6/2016	12345	55140
12/7/2016	12345	88978
12/8/2016	12345	89324
12/9/2016	12345	26173
12/10/2016	12345	59700
12/11/2016	12345	82935
12/12/2016	12345	64540
12/13/2016	12345	99355
12/14/2016	12345	72151
12/15/2016	12345	64896
12/16/2016	12345	98963
12/17/2016	12345	6614
12/18/2016	12345	91711
12/19/2016	12345	1861
12/20/2016	12345	77544
12/21/2016	12345	79124
12/22/2016	12345	73478
12/23/2016	12345	13749
12/24/2016	12345	12037
12/25/2016	12345	76280
12/26/2016	12345	60449
12/27/2016	12345	92659
12/28/2016	12345	90484
12/29/2016	12345	23124
12/30/2016	12345	18919
12/31/2016	12345	10153
12/1/2016	67890	64334
12/2/2016	67890	23919
12/3/2016	67890	64124
12/4/2016	67890	16427
12/5/2016	67890	92575
12/6/2016	67890	93357
12/7/2016	67890	61797
12/8/2016	67890	98938
12/9/2016	67890	34467
12/10/2016	67890	90933
12/11/2016	67890	71387
12/12/2016	67890	33188
12/13/2016	67890	26750
12/14/2016	67890	36846
12/15/2016	67890	37498
12/16/2016	67890	26453
12/17/2016	67890	59530
12/18/2016	67890	72568
12/19/2016	67890	13294
12/20/2016	67890	38653
12/21/2016	67890	46135
12/22/2016	67890	6750
12/23/2016	67890	26677
12/24/2016	67890	97527
12/25/2016	67890	13703
12/26/2016	67890	54050
12/27/2016	67890	31225
12/28/2016	67890	811
12/29/2016	67890	41234
12/30/2016	67890	52108
12/31/2016	67890	89277

[Phil_Seamark · Accepted solution]

Hi @ovetteabejuela,

I have it working with the following measure.  It won't work if you try to add as a column.

MTD Sales = 
    CALCULATE(
            SUM(Employee[Daily Total Sales]) ,
            FILTER (
				ALL('Employee'[Date]) , 
				'Employee'[Date] <= MAX('Employee'[Date])
				)
				
				) 

You can see this working in the following Matrix visual

[Sean · Accepted solution]

@ovetteabejuela

Here's the Measure that should do this

Running Total MEASURE = 
CALCULATE (
    SUM ( 'Table'[Daily Total Sales] ),
    VALUES ( 'Table'[Employee] ),
    FILTER ( ALL ( 'Table' ), 'Table'[Date] <= MAX ( 'Table'[Date] ) )
)

Hope this helps!

EDIT:

And if for some reason you want a COLUMN

Running Total COLUMN = 
CALCULATE (
    SUM ( 'Table'[Daily Total Sales] ),
    ALLEXCEPT('Table', 'Table'[Employee] ),
    'Table'[Date] <= EARLIER ( 'Table'[Date] )
)

[Phil_Seamark]

Do you sleep @Sean ?

[ovetteabejuela]

---

@Phil_Seamark wrote:

Do you sleep @Sean ?

---

LOL

[Phil_Seamark]

Maybe he can answer tricky questions like this in his sleep 🙂

[Sean]

@Phil_SeamarkAre you headed out for dinner?

[Phil_Seamark]

Not on a Monday night. 🙂

[ovetteabejuela]

Okay, I can confirm that this is working and it's actually awesome! I added a little more filter so that it would reset the count in each month and I got this, I'll do a little more modification so that it would reset per month per year...

MTD Sales =
CALCULATE (
    SUM ( employee[Daily Total Sales] ),
    FILTER (
        ALL ( 'employee'[Date] ),
        'employee'[Date] <= MAX ( 'employee'[Date] )
            && MONTH ( 'employee'[Date] ) <= MONTH ( MAX ( 'employee'[Date] ) )
    )
)

Thanks @Sean & @Phil_Seamark

[Phil_Seamark · Accepted solution]

Hi @ovetteabejuela,

I have it working with the following measure.  It won't work if you try to add as a column.

MTD Sales = 
    CALCULATE(
            SUM(Employee[Daily Total Sales]) ,
            FILTER (
				ALL('Employee'[Date]) , 
				'Employee'[Date] <= MAX('Employee'[Date])
				)
				
				) 

You can see this working in the following Matrix visual