Forum Discussion

mangopop's avatar
mangopop
Frequent Visitor
9 years ago

calculate average over period

Hello,

 

I'm sure this has been answered but I cannot find a answer that suits my situation.

 

I'm trying to get an average over a date range but my results aren't correct?

 

month APR  may june july august sept oct

stock   4566 123  61    80   2642   109  91     8995

av.       17     0      0      0     10       0     0       4

 

I'm not sure why the formula isn't using the data I've provided? (stock)

 

Currently I've setup a measure to do the following

 

 

 

6MonthAverage = CALCULATE (
  AVERAGE( SOPInvoiceCreditLine[stock] ),
   DATESBETWEEN(SOPInvoiceCreditLine[InvoiceCreditDate],DATE(2016,04,1),TODAY() 
  )
)

Thanks

 

2 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi mangopop,

     

    Most Time Intelligence Functions(including DATESBETWEEN function used in your formula) in Dax require an individual and consecutive Date Table to work correctly. If you don't have the Date table yet, you can use CALENDAR or CALENDARAUTO to create it. And create a relationship between the Date table and your fact table use the date key column. Then the formula should work correctly.

    6MonthAverage =
CALCULATE (
    AVERAGE ( SOPInvoiceCreditLine[stock] ),
    DATESBETWEEN ( DateTable[DateKey], DATE ( 2016, 04, 1 ), TODAY () )
)

    Regards

  • mangopop's avatar
    mangopop
    Frequent Visitor
    9 years ago

    The date column I'm referencing looks fine, SUM works using this method, it is just AVERAGE that is not returning correct results? I've ran against just 3 figures and it comes back skewed. The only way I've got it working is to do a manual sum

     

    6MonthAverage = CALCULATE (
    SUM( SOPInvoiceCreditLine[new quantity] ) / 6,
	DATESBETWEEN(SOPInvoiceCreditLine[InvoiceCreditDate],EOMONTH(TODAY(),-6),TODAY())
)