Creating a calculated column to find max date with filter

[karms88]

Hello all, 

I am trying to create a new column on my account table that will find the last date an account purchased a specific type of product. 

For reference, here are how the relationships are set up: 

The formula that I am currently trying is:

Last Purchase = CALCULATE(MAX(Invoice[Posting Date (Short Date)], FILTER('Product', LEFT('Product'[BC_Global_Dimension_2__c],4) = "2122")))

However, this is a returning an error code of : 

"A single value for column 'Posting Date (Short Date)' in table 'Invoice' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

I have another working column that I am basing this column off of. That column is looking to find the revenue an account has purchased for the same product group. That column is :

Total Product Group Revenue = CALCULATE(SUM('Invoice Line'[Line Amount]),LEFT('Product'[BC_Global_Dimension_2__c], 4) = "2122"))

Thank you all for the help!!

Kyle

[Anonymous · Accepted solution]

@karms88 

It is difficult to provide a solution without knowing the related columns. This could because you did not have a distinct date column as the calendar.

Try create a new date table= Distinct(Invoice[Posting Date (Short Date)]). And connect it new date table with the product table.

Then create your measure:

Last Purchase = CALCULATE(MAX(new date table[Posting Date (Short Date)]), FILTER('Product', LEFT('Product'[BC_Global_Dimension_2__c],4) = "2122"))

Best regards
Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@karms88 , Try the first one like

Last Purchase = CALCULATE(MAX(Invoice[Posting Date (Short Date)]), FILTER('Product', LEFT('Product'[BC_Global_Dimension_2__c],4) = "2122"))

[karms88]

@amitchandak, thanks so much for the rapid response. 

I have altered the column to what you have posted and this removed the error. However, the column is not accurate. It seems to be pulling the date from an accounts most recent invoice, whether that product group was on that invoice or not. Any idea what is causing that issue? 

Thanks, 

Kyle