Search code examples
dax

How do I return a column based on date filter

Sorry, new here (and to DAX in general!).

I am currently stuck trying to return a column of values based on a date. Take the following data example:

Date Value Category AvgValueForThisDate
05/02/2000 0.1 A ?
05/02/2000 0.2 B ?
06/02/2000 0.3 C ?
07/03/2001 0.2 C ?
08/03/2001 0.1 A ?
09/04/2001 0.2 B ?

So, I should be able to obtain for 05/02/2000, an array of:

Value
0.1
0.2

Which I can then average and place into the column AvgValueForThisDate.

Thanks.

So far, I am unsure what DAX functions I could potentially use to return the array to begin with.

Solution

  • You will need to break-out of the row-context to get the other rows' values.

    Create a Measure with:

    Avg Daily = 
  CALCULATE(
    AVERAGE('YourTable'[Value]),
    ALLEXCEPT('YourTable', 'YourTable'[Date])
  )