cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
OLoughanD Regular Visitor
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.

 

Capture.JPG

1 ACCEPTED SOLUTION

Accepted Solutions
HotChilli Established Member
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
HotChilli Established Member
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
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
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.
OLoughanD Regular Visitor
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))