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

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

Reply
Crydus
Helper I
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 KeyValid FromValid ToRemark
Item000101.04.202401.04.2025Valid in Future
Item000101.04.202301.04.2024Valid
Item000101.04.202201.04.2023Not Valid
Item000201.04.202401.04.2025Valid in Future
Item000201.04.2023 Valid
Item000201.04.2022 Not Valid
Item000301.04.2024 Valid in Future
Item000301.04.2023 Valid
Item000301.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
amitchandak
Super User
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]) 

View solution in original post

1 REPLY 1
amitchandak
Super User
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]) 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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