Max month which is not null value

[senerustunerciyas]

Hello team,

I want to create a month variable based on the last filled value.

For this, I used the FirstSortedValue(Ay,-aggr(Sum(Actual),Ay)) function in the first place.

But this function returned the month with the largest value. This worked at first but didn't work with some filters. Because at first we thought that the values were increasing cumulatively. But it is not like that. July can be bigger than August. But since the last filled month is August, I expect it to return in August.

[ajaykakkar93]

Hi,

Do you have a column like in last modified record with date or timestamp to identify the last month value is edited?

if your use case is like the max month with non-zero value?

[Gabbar]

The function you wrote will return AY with largest Sum(Actual),

If you want to return the last filed month convert month into num using num(month('Your Field'))  as "Month_Field" and use
FirstSortedValue(Ay,-aggr(max(Month_Field),Ay))

but when year changes it might happen that january will come after december but this formula will provide december,
So change it to year("Your Field")&num(month('Your Field')).

[senerustunerciyas]

I want to return the last month based on filed(not null) measure(Sum(actual)).

If the last sum (actual) value was entered in August, the month of August is returned. If the last sum(actual) value is September, I want the month of September to be returned.

[Gabbar · Accepted solution]

Then Try this :-
FirstSortedValue(Ay,-aggr(max({<Actual={"*"}>}Month_Field),Ay))
or if its numerical
FirstSortedValue(Ay,-aggr(max({<Actual={">=0"}>}Month_Field),Ay))

[vincent_ardiet_]

Maybe like this:
Max({<Ay={"=Fabs(Sum(Actual))>0"}>},Ay)