Forum Discussion
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 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.
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)
4 Replies
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)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!
- Anonymous try ALLSELECTED(data[date]) instead of ALL()