Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Noelle
Helper I
Helper I

Calculate average distinctcount with variable duration in the last 12 months

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

 

6 REPLIES 6
johnt75
Super User
Super User

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])

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.