Running Total and calculating the average for the month/week based on running total.

[Anonymous]

Hello,

Trying to wrap my head around running totals and calculating averages from the running total on a per week/month/year basis.

Example Data Set:

Billing Code    QTY    Running Total    Transaction Date

A1	1	1	01-Jan
A1	-1	0	02-Jan
A1	2	2	03-Jan
A1	1	3	04-Jan
A1	10	13	08-Jan

I have a transaction table showing a billing code 'A1' is being issued or returned based on the QTY. I have calculated the running total by creating a measure for this table.

Running Total = CALCULATE(Sum(TransactionTable['Qty'], FILTER( ALL('TransactionTable', 'TransactionTable'[TransactionDate] <= MAX('TransactionTable'[TransactionDate])

However, I don't know how to do another measure to average the running total on a per month/week/year basis.

Ex) If I were to average the Running total now, I should get 8.6.

1+0+2+3+13 / 5 = 8.6

I don't think this is what I want to do because it does'nt take into account the time period that A1 was deployed 13 times after January 8th and continued to be a value of 13 until January 31st.

I want to see:

Quantity    DeployedDate

1	01-Jan
0	02-Jan
2	03-Jan
3	04-Jan
3	05-Jan
3	06-Jan
3	07-Jan
13	08-Jan
13	09-Jan
13	10-Jan
13	11-Jan
13	12-Jan
13	13-Jan
13	14-Jan
13	15-Jan
13	16-Jan
13	17-Jan
13	18-Jan
13	19-Jan
13	20-Jan
13	21-Jan
13	22-Jan
13	23-Jan
13	24-Jan
13	25-Jan
13	26-Jan
13	27-Jan
13	28-Jan
13	29-Jan
13	30-Jan
13	31-Jan

Now with the average being 10.548 for the entire month of January.

What would be the best way to approach this problem?

1. I created my own Date Table and tried to do a running total with the given date attribute from January 1, 2007 to September 7, 2010 but the performance was really slow. I don't think it could even calculate the running total on this daily interval. Should even consider trying to get a running total on a per day basis from 2007?

Note that I do not want running total to reset per year. I need to it to calculate from the beginning of time and therefore use the ALL() in my filter when calculating.

2. How do you create another measure that requires information from another measure? Ex. Getting the average from the running totals

Is there another approach to getting the average for running totals for deployed units/quantities?

Thanks!

[v-frfei-msft]

Hi @Anonymous ,

We can use VALUES function to get the running total on a per year basis.

Running total = 
CALCULATE (
    SUM ( 'Table'[QTY] ),
    FILTER ( ALL ( 'date' ), 'date'[Date] <= MAX ( 'date'[Date] ) ),
    VALUES ( 'date'[Date].[Year] )
)

To get the correct average as we excepted, please check the following steps as below.

1. Create a calculated column in date table.

qty = 
LOOKUPVALUE ( 'Table'[QTY], 'Table'[Transaction Date], 'date'[Date] )

2. Create the measure as below.

average = 
VAR k =
    FILTER ( 'date', 'date'[qty] <> BLANK () )
RETURN
    DIVIDE (
        CALCULATE ( SUMX ( 'date', [Running total] ), KEEPFILTERS ( k ) ),
        CALCULATE ( COUNT ( 'date'[qty] ), KEEPFILTERS ( k ) )
    )

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

Tocreate a calculated column in date table and update the measure as below should be fine.

Week = WEEKNUM('date'[Date])

Running total = 
CALCULATE (
    SUM ( 'Table'[QTY] ),
    FILTER ( ALL ( 'date' ), 'date'[Date] <= MAX ( 'date'[Date] ) ),
    VALUES ( 'date'[Date].[Year] ),
VALUES ( 'date'[Date].[Month] ),
VALUES ( 'date'[Week])
)

[Anonymous]

Thanks @v-frfei-msft for the response. I've played around with the sample you have uploaded (Thanks btw!) but I can't seem to get the average for the entire month.

I want the average to equate to 10.548 as this is the average deployment over the month.

(Averaging the running total for the entire month)

How do I achieve the average for the running total of the entire month?

The average 3.8 seems to only apply to the individual dates and their related running totals.

(1+0+2+3+13) / 5 = 3.8

To extend this, I need to be able to get an average on a per week/per month basis.