Rolling All-Time Average Sales per Customer?

[AlexF_HH]

Hey PowerBi Experts, 

i checked the forum for ddifferent rolling average solutions.
But still can't figure out my (simple) case.

That's my test table in PowerBI.

ID: is salesorder ID
acc.id: is client
so.duration: is ordered duration

As you can see two clients ordered two campaigns each, one in 2020 an one in 2021.
avg.budget calculates the  'budget per order' / 'duration per order'.

Now i would like to calculate the 'global' average for each client (at the time of order creation).
Which means 'all-time budget per client' / 'all-time duration ordered per client'

So, for example the all ti,e average for the order BS01 would be = simple average (11.666,67)
but for BS02 it would be (35k+135k) / (3+3).

I was looking for ways to add a calculated column to manage this.
The best i could figure out is something like:

avg.global = calculate(sum(so.budget) / sum(so.duration), FILTER(saleorder, so.created <= so.created && acc.id = acc.id))

But i'm absolutely sure that my filter approach is wrong. And of course the result is wrong accordingly...

Hints are very appreciated 🙂

Thank you!

Alex

[AlexF_HH]

@Anonymous 

Thank you very much! It works 🙂

[Anonymous · Accepted solution]

// Your model should be a star-schema.
// If it's not, then you won't be able
// to create many measures and you will
// have problems with correct measures
// returning wrong figures. The measure
// I'm writing is for a correct model,
// not a single-table one.

// UTCD = up to current date
[Global Avg (UTCD)] =
// Assumption is Dates[Date] is the
// column that has an active one-to-many
// relationship with the fact table on
// the so.created column.
var LastVisibleDate = MAX( Dates[Date] )
var GlobalAvg =
	CALCULATE(
		DIVIDE(
			sum( FactTable[so.budget] ),
			sum( FactTable[so.duration] )
		),
		// For this to work Dates must
		// also be marked as a date table
		// in the model.
		Dates[Date] <= LastVisibleDate
	)
return
	GlobalAvg
	
// When you start slicing by accounts/clients,
// this will get you what you want but the
// meaning of this measure should be clear
// for all circumstances from the definition
// itself.