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

  • 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