Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 :
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
orders amount depending on duration = CALCULATE(DISTINCTCOUNT(sales[order number],
DATESBETWEEN( 'Date'[Date], DATE( YEAR(TODAY()) - 1, MONTH(TODAY()), DAY(TODAY()), TODAY()) )
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?
I think I have misunderstood what you are trying to do. Can you clarify please
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.
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.
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])
User | Count |
---|---|
25 | |
11 | |
7 | |
6 | |
6 |
User | Count |
---|---|
30 | |
13 | |
11 | |
9 | |
7 |