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
Icon for Community Champion rankCommunity Champion
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)
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Thanks, that definitely fixed the issue.  One side bug did pop up from this, and that is because we used the ALL functionality, it is no longer adaptive to the selected date range.  For example, if May 28th is my last date, but I filter down to May 20th - 25th, the visual will display 0 because May 28th (which is the identified last date) does not exist in the current filter.  Is there any way to bypass that?

 

Thanks again!

  • az38's avatar
    az38
    Icon for Community Champion rankCommunity 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!