Search code examples
exceldaxpowerpivot

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.

Solution

  • 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.