Return blank value or max date allexcept?

[murphm6]

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

[v-yangliu-msft · Accepted solution]

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

[amitchandak]

@murphm6,  a new column

maxx(filter(Table, [cust]=earlier([cust]) && [product] = earlier([product]) && not(isblank([to-Date])) ), [to-Date])

Earlier, I should have known Earlier: https://youtu.be/CVW6YwvHHi8
https://www.youtube.com/watch?v=cN8AO3_vmlY&t=17820s

[murphm6]

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