Help Needed on LASTNOBLANK dax function

[arkenstones]

Hi Friends,

It may sound silly but im facing a problem in calculating lastnoblank sales date - https://github.com/kumarabhinay/Power_BI/blob/main/LastNoBlank.pbix 
I should get Jan 3rd as the lastsale date but it get last sale date as Jan 4th. 

My Data - 

[arkenstones · Accepted solution]

Hi Everyone,

https://github.com/kumarabhinay/Power_BI/blob/main/FirstLastNoBlank.pbix

A simple calculate function over the expression fixed the issue. 

I tried so hard using AI but failed. This simple tutorial reminded the concept of filter context required for these set of functions to work 

Thank you very much!

[v-kpoloju-msft]

Hi @arkenstones,

Glad to hear that the issue is resolved and that adding the CALCULATE function helped. You are right. Functions like FIRSTNONBLANK and LASTNONBLANK depend heavily on filter context and wrapping them inside CALCULATE often clarifies that context to return the correct results.

Thanks for sharing your learning here it will surely help others facing a similar challenge.

[v-kpoloju-msft]

Hi @arkenstones, 

Thank you for reaching out to the Microsoft Fabric Community Forum. Also, thanks to @Ashish_Mathur, @Nasif_Azam, @Ahmedx, @Greg_Deckler, for those valuable insights on this thread.

Has your issue been resolved? If the response provided by the community member @Ashish_Mathur, @Nasif_Azam, @Ahmedx, @Greg_Deckler, addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.
Thank you for using the Microsoft Community Forum.

[arkenstones]

Hi,

Thank for reaching out.  I think i was not clear when i described the issue. I thank everyone for their ideas, suggestion and solutions. 

I look forward to learn from the them or the rest of the community. It's good to have a helping hand. I appreciate the efforts of each one of them and the miscrosoft communtity creator. 

Thank you again!

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[arkenstones]

Hi @Ashish_Mathur 

Thank you for your reply. The solution definately solves the issue but again I wanted to use lastnoblank() function to see if I can get the last date of sales. 

I think there is something wrong with my data. I will explore with another dataset and see if it works. 

Thank you very much!

[Nasif_Azam]

Hey @arkenstones ,

The reason you are getting Jan 4th instead of Jan 3rd as the last sale date is due to how LASTNONBLANK works in combination with SUM(Sales[SalesAmount]). On Jan 3rd, although there is a record for Product A, the quantity is zero, which results in a SalesAmount of zero. In DAX, LASTNONBLANK treats a zero result from the expression (in your case, SUM(Sales[SalesAmount])) as blank for the purpose of this function. So, LASTNONBLANK skips Jan 3rd and returns Jan 4th, which has actual non-zero sales amounts for both Product A and B. 

Last Sale Date (New) =
CALCULATE(
    MAX(Sales[Date]),
    FILTER(
        Sales, 
        NOT(ISBLANK(Sales[SalesAmount]))
    )
)

Please check the attached pbix file.

Best Regards,
Nasif Azam 

[arkenstones]

Hi @Nasif_Azam 

Thank you for your reply. Your solution is definately correct and does the job but Im exploring whether I can get the result for product B using LastNoBlank() Dax function like I got correct result for product A. Quantity column in the table has no use as I consider the salesamount as the product or price and quanity (which is confusing, sorry about that). 

Thank you very much!

[Ahmedx]

In your data the last sales date for A is Jan 4th

[Ahmedx]

If you want to get the previous figures for 0 sales, you can do it like this
or

or
am = 
 VAR _Am =  SUM(Sales[SalesAmount])
 RETURN
IF( ISBLANK(_Am), MAXX(
TOPN(1, FILTER(ALL(Sales), 'Sales'[Date]<MAX('Sales'[Date])),[Date],0),[SalesAmount])
,_Am
)

[arkenstones]

Hi @Ahmedx,

Thank you for your reply with the suggestions, but i was interesting in getting the lastsaledate using LastNoBlank() dax function. However, i definately learnt something from your solution.

Thank you very much!

[Ahmedx]

Ok, pls try this

 SalesDate= CALCULATE(LASTNONBLANK('Sales'[Date],CALCULATE(SUM(Sales[SalesAmount]))),'Sales'[Date]<MAX('Sales'[Date]))
----------------
 SalesNoBlank= CALCULATE(LASTNONBLANKVALUE('Sales'[Date],CALCULATE(SUM(Sales[SalesAmount]))),'Sales'[Date]<MAX('Sales'[Date]))

[Greg_Deckler]

@arkenstones Try this:

Last Sale Date Measure =
  VAR __Table = FILTER( 'Table', [SalesAmount] <> BLANK() )
  VAR __Result = MAXX( __Table, [Date] )
RETURN
  __Result

[arkenstones]

Hi @Greg_Deckler

I appreciate your solution, but i was trying to learn the usage of 

LASTNONBLANK() dax function but your recommendation does the job in an alternative way. 

Thank you so much!