The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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:
I have tried to use DATESINPERIOD, DATESBETWEEN, LASTDATE and ISINSCOPE but I don't have much experience with DAX
Thanks in advance.
Solved! Go to 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])
))
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]?
@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])
))
User | Count |
---|---|
27 | |
12 | |
8 | |
8 | |
5 |
User | Count |
---|---|
31 | |
15 | |
12 | |
11 | |
7 |