Forum Discussion

Noelle's avatar
Noelle
Icon for Helper I rankHelper I
4 years ago

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 f...
johnt75's avatar
johnt75
Icon for Super User rankSuper User
4 years ago

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

Noelle's avatar
Noelle
Icon for Helper I rankHelper I
4 years ago

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's avatar
    Noelle
    Icon for Helper I rankHelper I
    4 years ago

    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's avatar
      johnt75
      Icon for Super User rankSuper User
      4 years ago

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