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
orders amount depending on duration = CALCULATE(DISTINCTCOUNT(sales[order number], 
DATESBETWEEN( 'Date'[Date], DATE( YEAR(TODAY()) - 1, MONTH(TODAY()), DAY(TODAY()), TODAY()) )
Noelle's avatar
Noelle
Icon for Helper I rankHelper I
4 years ago

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'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.