Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Latest Available Month By Customer

I have a table named All Customer Files which contains the sales value column, [Value] and the date column, [End Date]. There is also a column that includes the names of various customers named [Customer]. 
 
I want to create a measure that shows the latest available month of sales data by customer. For example if Customer A, B and C's last month is Dec and Customer D's is Oct, then I want those months to display for each customer.
 
The best, I could come up with is the below measure, which identifies the last available month as December and shows only Customer A, B and C. How do I get the measure to filter for the last available month by customer?
 
Current Month = CALCULATE(SUM('All Customer Files'[Value]),FILTER('All Customer Files',MONTH('All Customer Files'[End Date].[Date])=MONTH(MAX('All Customer Files'[End Date]))))
Show and Tell

3 Replies

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    6 years ago
    Try something like:
    Format(Calculate(max(table[date]),allexcept(table[customer])), "mmm yyyy")
    This can be either as a measure or a calculated column.
    • danextian's avatar
      danextian
      Icon for Super User rankSuper User
      6 years ago
      Sorry. Should have been allexcept(table, table[customer]).
  • Tahreem24's avatar
    Tahreem24
    Icon for Super User rankSuper User
    6 years ago

    Anonymous ,

     

    Create a measure like below:

    Max_Date = MAX('Table'[DateColumn])
     
    Then Take one table and put this measure and name of custome so you'll get the latest dates against each customer. (refer below screen shot)
     
    Don't forget to give thumbs up 👍 and accept this as a solution if it helped you.