topic Last date of any selected period in DAX Commands and Tips

Last date of any selected period

jagutierrezm — Fri, 06 Nov 2020 16:38:54 GMT

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:

If you have a one Slicer per month and I select April that returns April 30
If I have a one Slicer per month and I do not select Month that returns the last date
If you have one Slicer per Week and select Agu-31 - Sep-06 that returns September 06
If I have a Slicer per Week and I do not select Range of the week that the last date returns
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.

Re: Last date of any selected period

amitchandak — Fri, 06 Nov 2020 16:49:41 GMT

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

Re: Last date of any selected period

jagutierrezm — Fri, 06 Nov 2020 23:31:54 GMT

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]?