Condition averaging calculation

[rnian18]

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 the last date).

My formula is:

test = CALCULATE( AVERAGE( 'data'[Sales] ), 'data'[Date] = LASTDATE('data'[Date]))

But this does not work.  However, if I manually enter the last date, it does work.  For example: 

test = CALCULATE( AVERAGE( 'data'[Sales] ), 'data'[Date] = Date(2020, 5, 28) )

The second calculation works, but it will only work when the last date is May 28th.  I am trying to have my dashboard figure out what the last date is, and pull that data and was wondering what I am doing wrong.

[az38 · Accepted solution]

Hi @rnian18 

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)

[rnian18]

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]

@rnian18 try ALLSELECTED(data[date]) instead of ALL()

[rnian18]

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