Running Total

[OLoughanD]

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.

[v-lili6-msft]

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

[Ashish_Mathur]

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.

[HotChilli · Accepted solution]

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]

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