Forum Discussion

Leigh_G's avatar
Leigh_G
Frequent Visitor
5 years ago
Solved

Max Value Associated with Most Recent Date

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?
 
 
 
Need Help
  • Mikelytics's avatar
    Mikelytics
    5 years ago

    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.

1 Reply

  • Mikelytics's avatar
    Mikelytics
    Icon for Resident Rockstar rankResident Rockstar
    5 years ago

    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.