cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
joetech
Frequent Visitor

Min date where date is greater than help

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.

1 ACCEPTED SOLUTION
Richard_100
Resolver I
Resolver I

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:

 

Richard_100_0-1644321301389.png

 

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])

 

Richard_100_1-1644321446011.png

 

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

View solution in original post

4 REPLIES 4
Richard_100
Resolver I
Resolver I

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:

 

Richard_100_0-1644321301389.png

 

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])

 

Richard_100_1-1644321446011.png

 

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

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
Solution Sage
Solution Sage

Hi @joetech - 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

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

Item table

Accountiditemidcreated
4561232/3/2020
4561241/1/2020
45621312/1/2019
...  
9525318/5/2020

Account Table

AccountidStart Date
1231/1/2020
4562/2/2020
7894/12/2020
1638/4/2020
9528/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

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors