Finding last value of Field which isnt zero

[Danqlik]

Hi Everyone,

here is an example of one of the fields in my data set:

Net Loss due to non Performing Loan
15,295
137,229
259,184
379,236
512,616
791,073
1,184,678
1,565,043
1,864,748
1,200,000
-
-
-
-
-
-

I want to be able to pull the last value in the field that isnt zero i.e. 1,200,000. 

To give context, the blank values are future dates hence the null values, but need to exists for the purpose of other fields within the larger data set.

Thanks in advance

[Almen]

I assume the date and field are both defined within the same table in the script. 

If the last date is today() you can use Sum({<date={"$(=today())"} >}Value) to retrieve the latest value.

[Danqlik]

My date field is in 'MMM-YY' format, so would the today() function still work? or would i need to include some sort of monthstart() function in there?

[Almen]

Ok, you can still use today() but needs a little tweaking:

Sum({<date={"$(=Date(today(),'MMM-YY'))"} >}Value)

[Danqlik]

Hi - thanks so much for helping. Theres another dimension to the issue that i forgot to mention, apologies. The last value which isnt zero would not always be the most recent data i.e todays data. Some files which will be uploaded into the dashboard will be matured data. i.e. no null values but last data point could be from a few years ago. How would i adapt the formula to compensate for this?

[Almen]

In that case you can use FirsSortedValue:

FirstSortedValue(Value, -DateField)

I assume you have a dimension so you can distinguish the different file uploads.

You would create a table with said dimension and put the firstsorted expression as a measure.

Notice the - before DateField so you get the latest value.