CODEWITHSUNDEEP
exceldaxpowerpivot
COG
COG

Reputation: 301

DAX formula to obtain latest month

I have a Calendar table and a Transactions table. The Transactions table includes a Date field which is joined to the Date field in the Calendar table. The Calendar table also includes a Period field (I said "month" in the title, but it could be a fiscal calendar). Now the Transactions table will only include transactions up to a certain date. For that given date I just need a DAX formula to pick up the corresponding Period from the Calendar table. It should be easy, but I can't for the life of me figure it out. Thanks.

Upvotes: 0

Views: 197

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

You should be able to get the last transaction date and then do a lookup roughly like this.

CalendarPeriod =
VAR LastTransDate = LASTDATE(Trans[Date])
RETURN LOOKUPVALUE(Calendar[Period], Calendar[Date], LastTransDate)

You may need to tweak the definition of the LastTransDate variable if, for example, you don't want it to use the filter context when calculating the max.

Upvotes: 1

Related Questions