Forum Discussion

arkenstones's avatar
arkenstones
New Member
10 months ago
Solved

Help Needed on LASTNOBLANK dax function

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 - 

 

 

Need Help

14 Replies

    • v-kpoloju-msft's avatar
      v-kpoloju-msft
      Community Support
      10 months ago

      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.

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    10 months ago

    arkenstones Try this:

    Last Sale Date Measure =
  VAR __Table = FILTER( 'Table', [SalesAmount] <> BLANK() )
  VAR __Result = MAXX( __Table, [Date] )
RETURN
  __Result
    • arkenstones's avatar
      arkenstones
      New Member
      10 months ago

      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! 
    • Ahmedx's avatar
      Ahmedx
      Super User
      10 months ago

      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's avatar
        arkenstones
        New Member
        10 months ago

        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!

  • Nasif_Azam's avatar
    Nasif_Azam
    Super User
    10 months ago

    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's avatar
      arkenstones
      New Member
      10 months ago

      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!

       




    • arkenstones's avatar
      arkenstones
      New Member
      10 months ago

      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!

  • v-kpoloju-msft's avatar
    v-kpoloju-msft
    Community Support
    10 months ago

    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's avatar
      arkenstones
      New Member
      10 months ago

      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!