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

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