Find first date in table where date greater than invoice date

[LotteLaugesen]

Hi

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

[tamerj1]

Hi @LotteLaugesen 
You can use

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

[LotteLaugesen]

Hi tamerj1

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

But thanks anyway

[tamerj1 · Accepted solution]

Try

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

[LotteLaugesen]

Same result - but thanks for trying 👍

[tamerj1]

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! 

[LotteLaugesen]

Here is my datamodel and contents of NFV Interest Rates:

And this is the calculation and result

Thanks for your time 🙂

[tamerj1]

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

[LotteLaugesen]

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

But look here:

[tamerj1]

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

[LotteLaugesen]

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  

[amitchandak]

@LotteLaugesen , You can create a new measure

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

[LotteLaugesen]

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