Last date of any selected period

[jagutierrezm]

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.

[jagutierrezm]

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 · Accepted solution]

@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])
))