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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
jagutierrezm
Frequent Visitor

Last date of any selected period

Hello a greeting,

 

How to get the last date of any selected period?

Regardless of whether you select Month, Week, Day, Year, Quarter, etc.

 

I would use this date in a measure that goes against an Inventory Stock table and I want to bring the inventory of the last date of the selected period.

 

the following measures for the week but these measures should work for other periods (Month, Year, etc.):

 

Inventory Units = sum ('Stock ABC'[Quantity])

 

Inventory Units Week =
CALCULATE ([Inventory Units],
FILTER ('Stock ABC', 'Stock ABC'[Date] = LASTDATE (Date [SF Weekend])
))

 

Date [SF Weekend] is a field in the Date table that has the date of the weekend

 

Examples:

  1. If you have a one Slicer per month and I select April that returns April 30
  2. If I have a one Slicer per month and I do not select Month that returns the last date
  3. If you have one Slicer per Week and select Agu-31 - Sep-06 that returns September 06
  4. If I have a Slicer per Week and I do not select Range of the week that the last date returns
  5. The same would apply for Year, Quarter, etc.

I have tried to use DATESINPERIOD, DATESBETWEEN, LASTDATE and ISINSCOPE but I don't have much experience with DAX

 

Thanks in advance.

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@jagutierrezm , You have a few options

your formula seems fine

lastnonblankvalue('Stock ABC'[Date],[Inventory Units])
or
Inventory Units Week =
CALCULATE ([Inventory Units],
FILTER ('Stock ABC', 'Stock ABC'[Date] = LASTDATE (Date [SF Weekend])
))
or
Inventory Units Week =
CALCULATE ([Inventory Units],
FILTER ('Stock ABC', 'Stock ABC'[Date] = max(Date [SF Weekend])
))
Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

2 REPLIES 2
jagutierrezm
Frequent Visitor

Hello Thanks for the Help, from the options you gave me the first one gave me the correct results with the data that I have.

 

I wanted to ask you the following:

 

La medida 

lastnonblankvalue('Stock ABC'[Date],[Inventory Units])

 

use the field 'Stock ABC' [Date] ==> I have this field hidden in the field list.

I have a table of Dates:

Could you substitute 'Stock ABC' [Date] for 'Date' [Date]?

I did the test and they give me the same results but what would be the best practice?

Always use 'Date' [Date]?

amitchandak
Super User
Super User

@jagutierrezm , You have a few options

your formula seems fine

lastnonblankvalue('Stock ABC'[Date],[Inventory Units])
or
Inventory Units Week =
CALCULATE ([Inventory Units],
FILTER ('Stock ABC', 'Stock ABC'[Date] = LASTDATE (Date [SF Weekend])
))
or
Inventory Units Week =
CALCULATE ([Inventory Units],
FILTER ('Stock ABC', 'Stock ABC'[Date] = max(Date [SF Weekend])
))
Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.