Forum Discussion

murphm6's avatar
murphm6
Icon for Helper II rankHelper II
3 years ago
Solved

Return blank value or max date allexcept?

Hi all,

 

I have a table that shows customers and product combinations over the years. I'm trying to return the most up-to-date price for each customer-product combination. 

 

The table format is below. I want to calculate the max date of the 'To-Date' in a new column so i can summarize the table and pull the latest price into my summarized table. If the 'To-Date' is blank, i want to pull that over any other date listed, because that is the most up-to-date data. How can i return the max date for cust-prod combos in a new calculated column while prioritizing blank values? Examples of desired results are below

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi  murphm6 ,

     

    Here are the steps you can follow：

    1. Create calculated column.

    Column =
var _maxfromdate=MAXX(FILTER(ALL('Table'),'Table'[Cust]=EARLIER('Table'[Cust])&&'Table'[Product]=EARLIER('Table'[Product])),[From-Date])
var _maxtodate=MAXX(FILTER(ALL('Table'),'Table'[Cust]=EARLIER('Table'[Cust])&&'Table'[Product]=EARLIER('Table'[Product])&&'Table'[From-Date]=_maxfromdate),[To-Date])
return
_maxtodate

    2. Result:

    When the maximum date is empty:

     

    Maximum date when data is available:

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

3 Replies

    • murphm6's avatar
      murphm6
      Icon for Helper II rankHelper II
      3 years ago

      Hey amitchandak , i tried this solution and it's still just taking the max date. For cust-prod combinations where there is a blank to-date, it's not returning a blank value

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi  murphm6 ,

     

    Here are the steps you can follow：

    1. Create calculated column.

    Column =
var _maxfromdate=MAXX(FILTER(ALL('Table'),'Table'[Cust]=EARLIER('Table'[Cust])&&'Table'[Product]=EARLIER('Table'[Product])),[From-Date])
var _maxtodate=MAXX(FILTER(ALL('Table'),'Table'[Cust]=EARLIER('Table'[Cust])&&'Table'[Product]=EARLIER('Table'[Product])&&'Table'[From-Date]=_maxfromdate),[To-Date])
return
_maxtodate

    2. Result:

    When the maximum date is empty:

     

    Maximum date when data is available:

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly