Forum Discussion
Resolver ILASTNONBLANK when expression only returns blank
Edited to add the answer: LASTNONBLANK() returns a blank when the expression only returns blank values.
---
What does LASTNONBLANK do when the expression only returns blank values?
For example, I have a Date field, a Customer field, and an Inventory field. Some customers report inventory daily, some report inventory associated with Tuesdays, some report inventory associated with Fridays, some report "on" Fridays but are several days late getting the data into the system.
I created a formula for LastInventoryDate = LASTNONBLANK(Date,Inventory)
I want to filter the date field for between 4/12 and 4/18.
Then in a matrix visual, I want to see each Customer and LastInventoryDate (the most recent date with non-blank inventory for that customer).
If the customer has inventory every day from 4/12 to 4/21, it should report 4/18, since that's the newest date in the specified range with non-blank inventory.
If a customer has inventory only loaded into Tuesdays, it should report 4/15, since 4/22 is outside of the specified range.
If a customer has inventory only loaded into Fridays, it should report 4/18.
If they were supposed to load into Fridays but haven't gotten around to it yet... What does LASTNONBLANK return?
LASTNONBLANK() returns a blank when the expression only returns blank values.
4 Replies
- Ashish_Mathur
Super User
Hi,
Could you share some data to work with and show the expected result. Share data in a format that can be pasted in an MS Excel file.
- Jihwan_Kim
Hi,
I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.
Please check the links down below from Microsoft Learn, and please compare the two DAX functions.
LASTNONBLANKVALUE function (DAX) - DAX | Microsoft Learn
LASTNONBLANK function (DAX) - DAX | Microsoft Learn
inventory expression: = // semi-additive SUM(inventory_fact[inventory])Lastnonblank measure: = // it returns the last date within the selected range when the inventory was not blank LASTNONBLANK(calendar_dim[date], [inventory expression:] )Lastnonblankvalue measure: = // it returns inventory value of the lastnonblank date LASTNONBLANKVALUE(calendar_dim[date], [inventory expression:] ) - v-csrikanth
Community Support
Hi shadowsong42
Thank you for being part of the Microsoft Fabric Community.
As highlighted by Jihwan_Kim Ashish_Mathur , the proposed approach appears to effectively address your requirements. Could you please confirm if your issue has been resolved?
If you are still facing any challenges, kindly provide further details, and we will be happy to assist you.
Best Regards,
Cheri Srikanth - shadowsong42
LASTNONBLANK() returns a blank when the expression only returns blank values.
