Forum Discussion

OLoughanD's avatar
OLoughanD
Helper I
7 years ago
Solved

Running Total

Want to get a running total for each date by SalesReps, ordering by date (ie. Month column) ascending. I have highlighted in red what I am trying to achieve.

 

Need Help
  • HotChilli's avatar
    HotChilli
    7 years ago
    RunningTote = VAR _SPerson = TableX[sPerson]
              VAR _Day = TableX[day]
RETURN
    CALCULATE(SUM([dTarget]), 
        FILTER(TableX,
            TableX[day] <= _Day && 
            TableX[sPerson] = _SPerson))

    Create a calculated column, swap in your table and column names.

     

    Quick Tip : For better help, post sample data rather than a picture of the data

4 Replies

  • HotChilli's avatar
    HotChilli
    Community Champion
    7 years ago
    RunningTote = VAR _SPerson = TableX[sPerson]
              VAR _Day = TableX[day]
RETURN
    CALCULATE(SUM([dTarget]), 
        FILTER(TableX,
            TableX[day] <= _Day && 
            TableX[sPerson] = _SPerson))

    Create a calculated column, swap in your table and column names.

     

    Quick Tip : For better help, post sample data rather than a picture of the data

    • OLoughanD's avatar
      OLoughanD
      Helper I
      7 years ago

      HotChilliThank you very much. This worked perfectly. I added a column to my table and based on your proposed solution my final code was:

      RunningTote = VAR _SPerson = Target[SalesPerson]
      VAR _Day = Target[Date]
      RETURN
      CALCULATE(SUM([DateTarget]),
      FILTER(Target,
      Target[Date] <= _Day &&
      Target[SalesPerson] = _SPerson))
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    Hi,

     

    Try this:

     

    1. Create a Calendar Table and build a relationship from the Date column of the Data Table to the Date column of the Calendar Table
    2. In your visual, drag Date from the Calendar Table and SalesRep from the Data Table
    3. Write these measures

    Target = SUM(Data[DailyTarget])

    RunningTotal = CALCULATE([Target],DATESYTD(Calendar[Date],"31/12"))

     

    Hope this helps.

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    7 years ago

    hi, OLoughanD 

    You could use Time-intelligence functions: TOTALYTD, TOTALMTD

    measre=CALCULATE([DailyTarget],DATESYTD('Date'[Date]))

     

    or filter over the date column using FILTER called as below:

    Measure= CALCULATE( SUM(DailyTarget),  FILTER (ALLSELECTED ( 'Date'[Date] ), AND ( 'Date'[Date] <= MAX ( 'Date'[Date] ), YEAR ( 'Date'[Date] ) = YEAR ( MAX ( 'Date'[Date] ) ) ) ) )

    or

    Measure= CALCULATE( SUM([DailyTarget]),  FILTER (ALLSELECTED ( 'Date'[Date] ), AND ( 'Date'[Date] <= MAX ( 'Date'[Date] ), AND ( YEAR ( 'Date'[Date] ) = YEAR ( MAX ( 'Date'[Date] ) ), MONTH ( 'Date'[Date] ) = MONTH ( MAX ( 'Date'[Date] ) ) ) ) ) )

     

    Best Regards,
    Lin