Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
AdamD123
Helper I
Helper I

Calculating Number of Days to expiry, per lot

Hello,

I have used this forum many times for help but this is my first post as I am unable to see my issue, so hopefully someone can help me!

 

I am trying to calculate the number of days from an items expiry date, to today.  Each item, has multiple entries by 'Lot No.' and the days to expiry count is only taking into account the latest.

 

For example:

Item X has two lots:

  • LOT01 with an expiry 01/01/2025
  • LOT02 with an expiry 01/01/2026

Days to expiry (as of 01/05/2025) should show as:

  • LOT01 - 245
  • LOT02 - 620

However, it is only showing as the latest expiry:

  • LOT01 - 620
  • LOT02 - 620

I am using a new colum "=ItemLedgerEntries[expiration_date].[date]-today()"

Power BI - Query.PNG

Data is imported from Business Central.

Any help is greatly appreicated!

Thank you,

Adam

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@AdamD123 That's a bit strange. Perhaps try this variation first:

Column = ( [expiration_date] - today() ) * 1.

Otherwise, perhaps this measure:

Measure = ( MAX( 'ItemLedgerEntries'[expiration_date] ) - today() ) * 1.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

2 REPLIES 2
Greg_Deckler
Super User
Super User

@AdamD123 That's a bit strange. Perhaps try this variation first:

Column = ( [expiration_date] - today() ) * 1.

Otherwise, perhaps this measure:

Measure = ( MAX( 'ItemLedgerEntries'[expiration_date] ) - today() ) * 1.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler Hi Greg, the Column did not work but the measure did! Thank you so much for the quick reply and solution.

Power BI - Query.PNG

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.