Missing "LastNONBLANKVALUE" Dax function.

[SO]

Hello,

I am trying to find the last value from a table, and was experimenting with LastNonBlank and wasn't getting the correct result and then read some more and realized that I think I should be using LastNonBlankValue, but this Dax function is missing and is not in my latest version of PBI (downloaded January 2022).   Update... I have this function in my data sets that are not live (InService).  Does it not exist in Service? 

My data looks something like:

Location	ID	Model	Start Date	End Date	Count	Status
1	101	F	9/9/2021	9/24/2021	1	a
1	101	R	9/25/2021	6/30/2022	1	a
1	102	F	9/9/2021	10/1/2021	1	a
1	102	R	10/2/2021	10/30/2021	1	a
1	102	F	11/1/2021	6/30/2022	1	a
2	989	R	9/9/2021	6/30/2022	1	a
3	988	R	9/9/2021	6/30/2022	1	a
4	222	R	9/9/2021	11/17/2021	1	a
4	222	F	1/4/2022	6/30/2022	1	x

Using the LastNonBlankValue I was hoping to get to the table below so that I could then use this information to filter values in another table,  

Location	ID	Model	Start Date	End Date	Count	Status
1	101	R	9/25/2021	6/30/2022	2	a
1	102	F	11/1/2021	6/30/2022	3	a
2	989	R	9/9/2021	6/30/2022	1	a
3	988	R	9/9/2021	6/30/2022	1	a
4	222	F	1/4/2022	6/30/2022	2	x

My Measure is currently, 

# Last2 = LASTNONBLANK('SET'[Startdate], max(SET[Model]))

but this only gives me the starting date and does not filter.  I assume it is because I'm not looking at the value.  

Location	ID	Model	Start Date	End Date	Count	Status	Last
1	101	F	9/9/2021	9/24/2021	1	a	9/9/2021
1	101	R	9/25/2021	6/30/2022	1	a	9/25/2021
1	102	F	9/9/2021	10/1/2021	1	a	9/9/2021
1	102	R	10/2/2021	10/30/2021	1	a	10/2/2021
1	102	F	11/1/2021	6/30/2022	1	a	11/1/2021
2	989	R	9/9/2021	6/30/2022	1	a	9/9/2021
3	988	R	9/9/2021	6/30/2022	1	a	9/9/2021
4	222	R	9/9/2021	11/17/2021	1	a	9/9/2021
4	222	F	1/4/2022	6/30/2022	1	x	1/4/2022

I was looking at 
@mahoneypa

https://community.powerbi.com/t5/Desktop/Get-the-last-non-blank-value-of-a-column-with-DAX/td-p/1694...

and @DataInsights https://community.powerbi.com/t5/Desktop/How-to-get-the-lastnonblank-value-when-I-need-to-keep-sever... 

but can't seem to replicate the answer.  Thoughts?  

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[DataInsights]

Nice solution, @Ashish_Mathur. This helped sharpen my understanding of LASTNONBLANK.

In case anyone encounters a performance issue (see link below), here's an alternative approach:

Last Model = CALCULATE ( MIN ( Data[Model] ), Data[Start Date] = MAX ( Data[Start Date] ) )

Last Status = CALCULATE ( MIN ( Data[Status] ), Data[Start Date] = MAX ( Data[Start Date] ) )

The September 2021 release introduced the use of aggregation functions in CALCULATE filters when they are used in Boolean (True/False) expressions. The expression MAX ( Data[Start Date] ) evaluates in the filter context of the visual, resulting in the appropriate date being used for each Location and ID.

https://www.sqlbi.com/articles/optimizing-lastnonblank-and-lastnonblankvalue-calculations/ 

[SO · Accepted solution]

Dear Ashish Mathur, 

I am simply in awe!  Thank you so much!  It is going to take me some time to understand why and how this works.  Thank You - Thank You!!   

Here I was focusing on the one function and you found such an elegant approach.    This is so kind of you.  I very much appreciate this support.  People like you make this such a wonderful community!  

[Ashish_Mathur]

You are welcome.  Thank you for your kind words.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.