Picking the correct date from a grouping in query editor

[NickolajJessen]

Hi 

I'm fairly new to Power-Bi and i'm trying to create a filtering table containing Project_ID and a date.
In order to have a one-to-many relation i need to group by Projed_ID in this table. HOWEVER, i need to pick the correct date to be displayed.

The correct date is the one attatched to the last 10010 account (or just any 10010 really). If there is no 10010 account, it should take the last 30010. 

Image1

 

How do i go about this?

After grouping, the data looks like this:

In the 'custom' column i've put the MAX date, but this is not what i'm looking for, as certain costs (account 30010) can be registered after invoice (10010). I'm primarily looking for the date connected to 10010

Image2

[Nastasita]

as I understand, try to use advanced grouping and group by ProjectID and FinancialAccount and max aggregating for Date column

[NickolajJessen]

Hi @Nastasita

Won't this cause a date on a 30010 account to be prioritized over a 10010, when 30010 has a later date?

[Nastasita]

@NickolajJessen

No, you will have both rows - for account 30010  with max date and for account 10010 with max date

[NickolajJessen]

@Nastasita

Oh yea, you're right.

But i'm still looking for ONE row, that takes the date of 10010, if it's available. Otherwise it should take the date of 30010