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 las...
Need Help
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!