Max Value Associated with Most Recent Date

[Leigh_G]

I have a standalone table of data (no date table in model) that has one date per month/year and sales for that date.  I am trying to create a card with the most recent sales figure (which should filter by date when date is used as a filter).

Here is the table:

Here is the current report filtered by date and showing the current most recent period based on the date filter:

Here is the code being used to generate the Most Recent Period measure:

Most Recent Period = CALCULATE(MAX('Advanced Retail Sales'[Date]),ALL('Advanced Retail Sales'[Advanced Retail Sales]))

Here is the code for the Last Value measure that is not working - it generates the 379K value when it should be 352K:

Last Value =

VAR d=CALCULATE(MAX('Advanced Retail Sales'[Date]),ALLSELECTED('Advanced Retail Sales'[Advanced Retail Sales]))

RETURN

LASTNONBLANK('Advanced Retail Sales'[Advanced Retail Sales],MAX('Advanced Retail Sales'[Date])=d)

Any solution?

[Mikelytics · Accepted solution]

HI @Leigh_G ,

Please try the following formula which gives you the number of the last date. I tried it with the following data set.

 (my table name was t_7)

SUM_SALES_LAST_MONTH =

CALCULATE(

Sum(t_7[Sales]),

FILTER(t_7,t_7[Date]=MAX(t_7[Date])

)

)

P.S. The formula name SALES_LAST_DATE would fit better to what it does 🙂 but in the end this is what should work for what you requested.

Best regards

Mikelytics

Did I solve your request? Please mark my post as solution.

Appreciate your Kudos.