Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Condition averaging calculation

I am trying to get the average of a column based on a condition.  For example, I am trying to get the average of the "Sales" column given it is the last date of my month (there can be many entries at...
Need Help
  • az38's avatar
    az38
    6 years ago

    Hi Anonymous 

    there is a pretty good explanation https://docs.microsoft.com/en-us/dax/lastdate-function-dax

    When the current context is a single date, the date returned by the FIRSTDATE and LASTDATE functions will be equal.

    so, it looks like  in your case  'data'[Date] is always the LASTDATE

    try smth like

    test =
var _lastDate = CALCULATE(MAX('data'[Date]), ALL('data') )
RETURN
 CALCULATE( AVERAGE( 'data'[Sales] ), 'data'[Date] = _lastDate)
az38's avatar
az38
Community Champion
6 years ago
Anonymous try ALLSELECTED(data[date]) instead of ALL()
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Thanks az38!  ALLSELECTED(data[date]) was buggy, but ALLSELECTED('data') did the trick!