cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Running Sum Stops If Date is <= Today()

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Re: Running Sum Stops If Date is <= Today()

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] )
    )
)

 

 

Try my new Power BI game Cross the River

View solution in original post

7 REPLIES 7
Highlighted
Community Support
Community Support

Re: Running Sum Stops If Date is <= Today()

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

Community Support Team _ Xiaoxin
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Frequent Visitor

Re: Running Sum Stops If Date is <= Today()

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_CenterDateDay_of_MonthWeek_NumberYearMonthYear_MonthYear_Month_WeekCountProduced_QtyRunning Total
TEst1 148201712201712201712481 448800
TEst1 248201712201712201712481 448800
TEst1 349201712201712201712491 448800
TEst1 449201712201712201712491212400448800
TEst1 549201712201712201712491236400448800
TEst1 649201712201712201712491 448800
TEst1 749201712201712201712491 448800
TEst1 849201712201712201712491 448800
TEst1 949201712201712201712491 448800
TEst1 1050201712201712201712501 448800
TEst1 1150201712201712201712501 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_CenterDateDay_of_MonthWeek_NumberYearMonthYear_MonthYear_Month_WeekCountProduced_QtyRunning TotalDay_Of_Week
TEst1 148201712201712201712481 06
TEst1 248201712201712201712481 07
TEst1 349201712201712201712491 01
TEst1 4492017122017122017124912124002124002
TEst1 5492017122017122017124912364004488003
TEst1 649201712201712201712491  4
TEst1 749201712201712201712491  5
TEst1 849201712201712201712491  6
Highlighted
Super User III
Super User III

Re: Running Sum Stops If Date is <= Today()

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] )
    )
)

 

 

Try my new Power BI game Cross the River

View solution in original post

Highlighted
Frequent Visitor

Re: Running Sum Stops If Date is <= Today()

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

Highlighted
Frequent Visitor

Re: Running Sum Stops If Date is <= Today()

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] )
    )
)

Highlighted
Super User III
Super User III

Re: Running Sum Stops If Date is <= Today()

@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
        )
    )
Try my new Power BI game Cross the River
Highlighted
Frequent Visitor

Re: Running Sum Stops If Date is <= Today()

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

 

 

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors