Help with Formula for calculated column ( Calculate Price depending on period )

[Vrykolakas]

Hi all !
I'm stuck with the following problem.
I Have two excel files, two tables as showns bellow, One with "Price lists" and the second one has all "Orders". Now I need to get the prices into the "Orders" table, but following this logic :

• For the current FY, ( Genre and FY must match ) see if the OrderDate in "Orders" corresponds to which period ( "From" and "to" columns ) and then gets the right price
• This logic isn't needed for the other fiscal years
• The tables aren't related in a relationship

I'm stuck getting the right calculation for the calculated column Price (in yellow), in the "Orders" table. I'm also open to other users solution..
Looking forward to your comments

[v-xiaotang · Accepted solution]

Hi @Vrykolakas 

Please try this calculated column

Price = 
var _currentFY="22-23"
return IF(Orders[FY]=_currentFY,
MAXX(FILTER('List prices','List prices'[From]<=EARLIER(Orders[OrderDate]) && 'List prices'[To]>=EARLIER(Orders[OrderDate]) && 'List prices'[Genre]=EARLIER(Orders[Genre]) && 'List prices'[FY]=EARLIER(Orders[FY])),[Price]),
MAXX(FILTER('List prices','List prices'[Genre]=EARLIER(Orders[Genre]) && 'List prices'[FY]=EARLIER(Orders[FY])),[Price]))

Best Regards,

Community Support Team _Tang

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