Forum Discussion

jersey417's avatar
jersey417
Icon for Helper I rankHelper I
8 years ago
Solved

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 ...
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    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's avatar
jersey417
Icon for Helper I rankHelper I
8 years ago

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
Zubair_Muhammad's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity Champion
8 years ago

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's avatar
    jersey417
    Icon for Helper I rankHelper I
    8 years ago

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

    • jersey417's avatar
      jersey417
      Icon for Helper I rankHelper I
      8 years ago

      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's avatar
        Zubair_Muhammad
        Icon for Community Champion rankCommunity Champion
        8 years ago

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