cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

## Need Help with MAX Date with multiple conditions

Hello,

I need some help regarding a calculated column which returns me the MAX(X) date with a second set of filters. I have basic knowledge on how to use the MAXX()-Expression for that, but I don't know how and if I can add another filter to this expression.

Our company has a list of prices (item_customer_relationship) with valid_from and valid_to dates, with the limitation that valid_to is not really in use anymore (I will provide an example belw with the basic idea of this table.) My problem now is that I have to identify the current price which is valid.
I now need a calculated column which returns the values found in remark. The only thing I manage to get is the max valid_from Date per Item but I do not now how to include the valid_to date.

Currently our system basically has 3 options:
Item0001 is the best case, with all dates filled, Item0002 is the case where valid_to is implemented for the next price change, but currently it is not in use, Item0003 is the case where No valid_to date is provided.

 Item Key Valid From Valid To Remark Item0001 01.04.2024 01.04.2025 Valid in Future Item0001 01.04.2023 01.04.2024 Valid Item0001 01.04.2022 01.04.2023 Not Valid Item0002 01.04.2024 01.04.2025 Valid in Future Item0002 01.04.2023 Valid Item0002 01.04.2022 Not Valid Item0003 01.04.2024 Valid in Future Item0003 01.04.2023 Valid Item0003 01.04.2022 Not Valid

I hope that you can help me. Please feel free to ask for more information, I will provide as much as I can if anything is needed.

1 ACCEPTED SOLUTION
Super User

@Crydus , A new column in a sales/transaction table

Price from item to sales =

Maxx(filter(Item , Item[Item Key] = sales[Item Key] && Sales[Date]>= Item[Valid From] && Sales[Date]<= Item[Valid To]) , Item[Price])

Super User

@Crydus , A new column in a sales/transaction table

Price from item to sales =

Maxx(filter(Item , Item[Item Key] = sales[Item Key] && Sales[Date]>= Item[Valid From] && Sales[Date]<= Item[Valid To]) , Item[Price])

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors