Min date where date is greater than help

[Anonymous]

Hi Guys,

I've got a weird one. I've got two tables linked. I have an accounts table im creating a custom column based on their first item created (from a different item table), but I want the first item created after they start.

I'm doing the following calculated column on the accounts:

Earliest Response = CALCULATE(MINX('items',[created]), FILTER('items', 'Account'[startdate] < 'items'[created] && 'account'[id] = 'items'[accountid]))

All of the accounts are unique, and an account will have multiple items of course.

 It appears my filter of 'Account'[startdate] < 'items'[created] is being ignored entirely.

[Richard_100 · Accepted solution]

Hello

If I understand correctly, this is a row context thing.  Your calculated column is in Account but it is iterating Item within the FILTER function and it doesn't know which AccountID it needs.  So you can bring that in with a variable

This is my dummy setup:

They are related via Account ID but I'm not using that relationship.  You could probably achieve the below result with RELATEDTABLE, but I've done it this way:

MIN Item Created Date = 
VAR Acc_ID = Account[Accountid]
VAR Acc_Start = Account[Start Date]
VAR Tab = FILTER('Item', 'Item'[Accountid]=Acc_ID && 'Item'[created]>=Acc_Start)
RETURN MINX(Tab, 'Item'[created])

MIN Item Lookup = 
VAR Acc_ID = Account[Accountid]
VAR Acc_Start = Account[Start Date]
VAR Tar_Date = Account[MIN Item Created Date]
VAR Tab = FILTER('Item', 'Item'[created]=Tar_Date)
RETURN MINX(Tab, 'Item'[itemid])

The variables grab what you need from the row of Account in order to filter Item within the FILTER function.  I lookup the min date first, and then use that to identify the Item ID.  If you had multiple items on the same created date, it would return the min Item ID within that subset

Hope that helps

Regards

Richard

[Anonymous]

Hi Richard,

For some reason, it was the > between dates that wasn't working. I ended up creating a column on item that took the date from the account, then another that said if item date was greater than created date than 1, else 0, and then I did the filter on the 1 instead of the dates. Sloppy, but it worked.

[Daryl-Lynch-Bzy]

Hi @Anonymous - It might help to upload a sample pbix with the issue, or you show some sample data and your date model to help understand your problem.

Many thanks

Daryl

[Anonymous]

Hi Daryl, I can't really provide a model, but essentially its something like:

Item table

Accountid	itemid	created
456	123	2/3/2020
456	124	1/1/2020
456	213	12/1/2019
...
952	531	8/5/2020

Account Table

Accountid	Start Date
123	1/1/2020
456	2/2/2020
789	4/12/2020
163	8/4/2020
952	8/5/2020

Essentially, I want a custom column on the account table giving me the minimum date from the item table for that account, that is higher than the start date on the account table.. so for 456 it would be 2/3/2020