Finding value between dates

[jakub_klocek]

Hey,

first hello, it´s my first post here 🙂

I have my ('Order'[Product]) which has been sold on ('Order'[DateNew]).

Now I need to find ('Price(2)'[Old price]) based on date between ('Price(2)'[Old from]) and (Price(2)'[New from]).

If not found then newest ('Price(2)'[Old price]).

I created this formula, but SUM is not best expression.

Any ideas?

VK Neu = CALCULATE( SUM('Price(2)'[Old price]),

FILTER( 'Price(2)',

'Price(2)'[Old from] <= 'Order'[DateNew] &&

'Price(2)'[New from] >= 'Order'[DateNew] &&

'Price(2)'[Product] = 'Order'[Product]

))

Thanks!

Jakub

[amitchandak]

@jakub_klocek , are there two table Price and Price(2) ?

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[jakub_klocek]

Sorry, my bad, only one Price(2) Table.

[v-rzhou-msft · Accepted solution]

Hi @jakub_klocek ,

Is the result in your screenshot the result you want? I have test by your data and code. I think it works well if you build a calculated column.

Order Table:

Price(2) Table:

Add a calcualted column in Order Table.

VK Neu = 
CALCULATE (
    SUM ( 'Price(2)'[Old price] ),
    FILTER (
        'Price(2)',
        'Price(2)'[Old from] <= 'Order'[DateNew]
            && 'Price(2)'[New from] >= 'Order'[DateNew]
            && 'Price(2)'[Product] = 'Order'[Product]
    )
)

Output:

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.