Running Total (Editor/CalculatedColumn/Measure?) and Average Running Total (Measure?)

[Fraze]

Hello,

My current issue is 2-fold. I have figured out how to solve each independently, but none of the approaches I have found solve both simultaneously. Test file attached here

1 - Running Total of NetChange

• Needs to be filterable for any Date or CustNo; however, if for example June is selected, the Running Total needs to still include all previous data rather than starting again at $0 on June 1.
• My current solution is a measure, but it creates issues for my next challenge so I feel a calculated column might be required

2 - Dynamic Average of Running Total

• Need the Average Running Total for the date range selected, once again filterable by Date and CustNo. If june is selected, I need the average of 30 days worth of Running Total.
• Note, this is the Running Total average not the average of NetChange. So if there is only one June transaction on the 1st for $30,000, the Running Total Average for the month would be $30,000, NOT (30,000 + 0 + 0 + ...)/30    

Thanks!

[Anonymous]

Hi @Fraze ,

Do you want to calculate the average of running total by logic as running total divide days whose netchange not equal to 0?

Here I suggest you to try this code.

Avg = 
VAR _SUMMARIZE =
    SUMMARIZE (
        'Transactions&Date',
        [Date],
        "NetChange", CALCULATE ( SUM ( 'Transactions&Date'[NetChange] ) )
    )
VAR _COUNT_DAY =
    COUNTAX ( FILTER ( _SUMMARIZE, [NetChange] <> 0 ), [Date] )
RETURN
    DIVIDE ( [Running Total], _COUNT_DAY )

In June there are 24 days whose Netchange <>0.

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Fraze]

Hello,

No, I am looking for the basic average. So if we keep using June as the example, the average Running total is -53,271. So the average calculation formula is very basic, what is challenging is that it is doing it on the running total measure. Which is why I am also curious if the running total should be converted to a calcualted column.