Calculate average distinctcount with variable duration in the last 12 months

[Noelle]

Hi all,

I would like to calculate the amount of rows in a table listing orders (that will be "orders amount") , on a specific duration that is depending on "account name".

I've tried using the following :

orders amount depending on duration = CALCULATE(DISTINCTCOUNT(sales[order number], DATESINPERIOD('DATE'[Date], 'DATE'[Today], -MAX('sales'[payment terms in day]', day))

But the calculation is done on last periode (for example last 30 days), and it would be more accurate if I could calculate the average "order amounts/duration" on last 12 months.

Any idea? I'm stuck here 😞

Thank you in advance for your help.

Noelle

[johnt75]

orders amount depending on duration = CALCULATE(DISTINCTCOUNT(sales[order number], 
DATESBETWEEN( 'Date'[Date], DATE( YEAR(TODAY()) - 1, MONTH(TODAY()), DAY(TODAY()), TODAY()) )

[Noelle]

Hi @johnt75 thank you for your prompt help.

Won't that calculate all rows in between date instead of calculate average rows per payment terms in days?

[johnt75]

I think I have misunderstood what you are trying to do. Can you clarify please

[Noelle]

Let's say I have customerA, ordering 128 times in the last 12 months, payment term is 30 days. He orders 8 times per month, but one month he ordered 40 times. The average amount of orders during 30 days is not 128/12 (if we consider one month=30 days) It'll be more around 8 then. + the payment term "30 days) will be different according to each customer.

[Noelle]

I think I need to create a loop where, every day between today and 12 months ago, PBI will calculate amount of orders on the said duration (in my example above 30 days) and will return the average of those calculations.

[johnt75]

You could look to create buckets of the appropriate size for each customer. If you iterate over the customer table you could try something like

var paymentTerms = SELECTEDVALUE(Customer[payment terms])
var summaryTable = ADDCOLUMNS(
GENERATESERIES( TODAY() - ( 12 * paymentTerms ), TODAY(), paymentTerms),
"@num orders", CALCULATE( COUNTROWS( Orders), DATESBETWEEN( 'Date'[Date], [Value], [Value] + paymentTerms) )
)
return AVERAGEX( summaryTable, [@num orders])