Running Sum Stops If Date is <= Today()

[jersey417]

I've been beating my head around the following without getting

a solution. I've read through the boards without finding a similar question.

I created the following running sum calculated column within my table. However, as expected the running sum continues to input data beyond the current date. How would I change the calculated column below to stop the running sum based on <=today() ?

Running Total =

CALCULATE (

   SUM ( test1[Produced_Qty]),

   ALLEXCEPT ( test1, test1[Work_Center] ),

   test1[Date] <= EARLIER ( test1[Date] )

)

Any help is appreciated

[v-shex-msft]

Hi @jersey417,

You can try to use today function to replace the EARLIER part:

Running Total =
CALCULATE (
    SUM ( test1[Produced_Qty] ),
    FILTER ( ALLEXCEPT ( test1, test1[Work_Center] ), [Date] <= TODAY () )
)

If above not help, please share some sample data to test.

Regards,

Xiaoxin Sheng

[jersey417]

Xiaoxin,

It appears this didn’t solve the overall issues and created more. The code provided appears to have just summed the produced quantity for all dates vs creating a running sum that stops at today’s date.

Based on what you provided:

Work_Center	Date	Day_of_Month	Week_Number	Year	Month	Year_Month	Year_Month_Week	Count	Produced_Qty	Running Total
TEst1		1	48	2017	12	201712	20171248	1		448800
TEst1		2	48	2017	12	201712	20171248	1		448800
TEst1		3	49	2017	12	201712	20171249	1		448800
TEst1		4	49	2017	12	201712	20171249	1	212400	448800
TEst1		5	49	2017	12	201712	20171249	1	236400	448800
TEst1		6	49	2017	12	201712	20171249	1		448800
TEst1		7	49	2017	12	201712	20171249	1		448800
TEst1		8	49	2017	12	201712	20171249	1		448800
TEst1		9	49	2017	12	201712	20171249	1		448800
TEst1		10	50	2017	12	201712	20171250	1		448800
TEst1		11	50	2017	12	201712	20171250	1		448800

Redefining what I’m looking for – I’m looking for a running sum(running total column) where the running sum is defined by the following variables; Work Center and Date. However, I need the running sum to stop on today’s date. We have several work_center values and the running sum needs to be independent for each.

Example of what I'm looking for

Work_Center	Date	Day_of_Month	Week_Number	Year	Month	Year_Month	Year_Month_Week	Count	Produced_Qty	Running Total	Day_Of_Week
TEst1		1	48	2017	12	201712	20171248	1		0	6
TEst1		2	48	2017	12	201712	20171248	1		0	7
TEst1		3	49	2017	12	201712	20171249	1		0	1
TEst1		4	49	2017	12	201712	20171249	1	212400	212400	2
TEst1		5	49	2017	12	201712	20171249	1	236400	448800	3
TEst1		6	49	2017	12	201712	20171249	1			4
TEst1		7	49	2017	12	201712	20171249	1			5
TEst1		8	49	2017	12	201712	20171249	1			6

[Zubair_Muhammad · Accepted solution]

Hi @jersey417

Try this

Running Total =
IF (
    Test1[Date] <= TODAY (),
    CALCULATE (
        SUM ( test1[Produced_Qty] ),
        ALLEXCEPT ( test1, test1[Work_Center] ),
        test1[Date] <= EARLIER ( test1[Date] )
    )
)

[jersey417]

Perfect - This is the solution I was looking for. Thanks for the help.

[jersey417]

I just wanted to follow up on the following. I switched my data to a directquery and the following doens't appear to work due to the EARLIER function being used. Based on the information that I read online it appears that the EARLIER function is not avaialble when using a directquery. Is there away around this?

Running Total =
IF (
    Test1[Date] <= TODAY (),
    CALCULATE (
        SUM ( test1[Produced_Qty] ),
        ALLEXCEPT ( test1, test1[Work_Center] ),
        test1[Date] <= EARLIER ( test1[Date] )
    )
)

[Zubair_Muhammad]

@jersey417

Try

Running Total =
VAR myDate = test1[Date]
RETURN
    IF (
        Test1[Date] <= TODAY (),
        CALCULATE (
            SUM ( test1[Produced_Qty] ),
            ALLEXCEPT ( test1, test1[Work_Center] ),
            test1[Date] <= myDate
        )
    )

[jersey417]

I'm not getting the returned error of "Function "CALCULATE" is not allowed as part of calculated column DAX expression on DirectQuery models. "