Forum Discussion

jakub_klocek's avatar
jakub_klocek
Frequent Visitor
4 years ago
Solved

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
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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.