Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more

Reply
jakub_klocek
Frequent Visitor

Finding value between dates

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
1 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:

3.png

Price(2) Table:

1.png

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:

2.png

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.

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@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.

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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

 

jakub_klocek_0-1643973143110.png

 

 

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:

3.png

Price(2) Table:

1.png

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:

2.png

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.

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

Check out the March 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors
Top Kudoed Authors