topic Re: Show value only for the last quarter with values in DAX Commands and Tips

Show value only for the last quarter with values

Lovebo01 — Wed, 02 Sep 2020 16:38:15 GMT

Hi everyone,

I am trying to show the value for the last quarter selected by the user with values (non blank).

To show the value of the last quarter selected by the user I am using this function :

Value Last Quarter =

CALCULATE([Value],

Filter('Date','Date'[Quarter]=MAX('Date'[Quarter])))

So I tried to use the LASTNONBLANK function but when I am using it like this :

Value Last Quarter with values =

CALCULATE([Value],

Filter('Date','Date'[Quarter]=LASTNONBLANK('Date'[Quarter],[Value])))

It sums all the value for all the quarter. It doesn't sum only the last quarter.

Do you know how to do it please ?

Thank you for your help,

Lovebo,

Re: Show value only for the last quarter with values

amitchandak — Wed, 02 Sep 2020 16:47:18 GMT

Lovebo01 , try like

Value Last Quarter =
var _max =MAXX(allselected('Date'),'Date'[Quarter])
return
CALCULATE([Value],
Filter('Date','Date'[Quarter]=_max))

Re: Show value only for the last quarter with values

Lovebo01 — Thu, 03 Sep 2020 07:42:44 GMT

Hello,

Thank you for your help but it doesn't work, I have only values for the last Quarter, but if the last quarter with some values is the 2nd quarter, it shows nothing.

Re: Show value only for the last quarter with values

Lovebo01 — Thu, 03 Sep 2020 07:46:43 GMT

But it gave me an idea for the solution :

Value Last Quarter =

var _maxQuarter =MAXX(allselected('Date'),'Date'[Quarter])

var _lastQuarterWithValue = LASTNONBLANK('Date'[Quarter],[Value])

return

CALCULATE([Value],

Filter('Date','Date'[Quarter]=MIN(_maxQuarter,_lastQuarterWithValue)))

And this one works good ! 🙂

Thank you,