topic Re: Find first date in table where date greater than invoice date in DAX Commands and Tips

Find first date in table where date greater than invoice date

LotteLaugesen — Tue, 22 Feb 2022 11:38:19 GMT

How do I find max Valid from in the Interest table where Invoice date >= Valid from?
Invoice date = 2021-11-16 and I want to get row 2, Valid from 2021-11-15
Interest table

Any ideas?

Lotte

Re: Find first date in table where date greater than invoice date

amitchandak — Tue, 22 Feb 2022 12:49:26 GMT

LotteLaugesen , You can create a new measure

calculate(sum(Table[Intrest]), filter(Table, Table[Valid from] >= max(Invoice[Invoice Date]) ) )

Re: Find first date in table where date greater than invoice date

LotteLaugesen — Tue, 22 Feb 2022 13:16:01 GMT

Unfortunately it doesn't work. I get the sum of all interest

Re: Find first date in table where date greater than invoice date

tamerj1 — Tue, 22 Feb 2022 13:32:51 GMT

Hi LotteLaugesen
You can use

Intrest Lookup Date = MAXX ( FILTER ( Table, Table[Valid from] <= MAX ( Invoice[Invoice Date] ) ), Table[Valid from] )

Re: Find first date in table where date greater than invoice date

LotteLaugesen — Tue, 22 Feb 2022 14:57:24 GMT

Hi tamerj1

Unfortunately I then get 2021-12-20 in return and not 201-11-15

But thanks anyway

Re: Find first date in table where date greater than invoice date

tamerj1 — Wed, 23 Feb 2022 04:46:14 GMT

Try

Intrest Lookup Date = MAXX ( FILTER ( ALL ( Table ), Table[Valid from] <= MAX ( Invoice[Invoice Date] ) ), Table[Valid from] )

Re: Find first date in table where date greater than invoice date

LotteLaugesen — Wed, 23 Feb 2022 07:20:41 GMT

Same result - but thanks for trying 👍

Re: Find first date in table where date greater than invoice date

tamerj1 — Wed, 23 Feb 2022 09:09:17 GMT

Hi LotteLaugesen
I'm getting correct results. See the screen shot. But I think it depends on your data model and relationships. Most probably you have a connected date table. Can you please send a screen shot of your data model with the relationships?
Thanks and have a nice day!

Re: Find first date in table where date greater than invoice date

LotteLaugesen — Wed, 23 Feb 2022 09:34:30 GMT

Here is my datamodel and contents of NFV Interest Rates:

And this is the calculation and result

Thanks for your time 🙂

Re: Find first date in table where date greater than invoice date

tamerj1 — Wed, 23 Feb 2022 10:34:00 GMT

LotteLaugesen
Are you sure invoice date is of date data type? It looks like it is of text data type 🙂

Re: Find first date in table where date greater than invoice date

LotteLaugesen — Wed, 23 Feb 2022 10:41:36 GMT

I'm wondering why it looks like that, too...

But look here:

Re: Find first date in table where date greater than invoice date

tamerj1 — Wed, 23 Feb 2022 10:47:43 GMT

Check the data source from power query. Or if you don't mind can you please a sample data version of the file?

Re: Find first date in table where date greater than invoice date

LotteLaugesen — Wed, 23 Feb 2022 11:06:33 GMT

Sorry about all this - I had made the calculation as a new column in the Merge ARAP and SCF table and not as a new measure 🙈 So after changing that it works perfectly
Thanks for all you time