How to refer to a single value in a Calculated Column in a measure (DAX)

Question

I have a table(Data_all) that calculates daycount_ytd in one table.

[Date] is in Date Format. 
[Fiscal Year] is just year. eg: 2016

Calculated Column
daycount_ytd=DATEDIFF("01/01/"&[Fiscal Year],Data_all[Date],day)+1

Im trying to create a measure that refers to this Calculated Column

Measure:
Amt_X Yield %:=[Amt X]/([Amt Y]/365* (Data_all[DayCount_YTD]))

I get the error that Data_all[DayCount_YTD] refers to a list of values. How do i filter the expression to get a single value without using a aggregation function eg:(sum, median)?

Or perhaps, is there another way to achieve the same calculation?

Jacob Jacob · Accepted Answer · 2016-06-22T18:26:00

You've arrived an a fundamental concept in DAX and once you've worked out how to deal with it then the solution generalises to loads of scenarios.

Basically you can't just pass columns into a DAX measure without wrapping them in something else - generally some kind of mathematical operation or you can use VALUES() depending on exactly what you are trying to do.

This measure will work OK if you use it in a PIVOT with the date as a row label:

=
  SUM ( data_all[Amt X] )
   / (
      SUM ( data_all[Amt Y] ) / 365
      * MAX ( data_all[daycount_ytd] )
      )

However you will see it gives you an incorrect total as it is in the latest for the entire thing. What you need is a version that iterates over the rows and then performs a calculation to SUM or AVERAGE each item. There is a whole class of DAX functions dedicated to this such as SUMX, AVERAGEX etc. You can read more about them here

It's not totally clear to me what the maths behind your 'total' should be but the following measure calculates the value for each day and sums them together:

=
  SUMX(
      VALUES(data_all[date]), 
         SUM(data_all[Amt X]) / 
         (SUM(data_all[Amt Y]) / 365 * MAX(data_all[daycount_ytd]))
       )

How to refer to a single value in a Calculated Column in a measure (DAX)

1 Answers