cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

## 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.

1 ACCEPTED SOLUTION

Accepted Solutions
Established Member

## Re: Running Total

```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 4
Established Member

## Re: Running Total

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

Super User

## Re: Running Total

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.

Community Support Team

## Re: Running Total

hi, @OLoughanD

You could use Time-intelligence functions: TOTALYTDTOTALMTD

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

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

## Re: Running Total

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