Forum Discussion

Vrykolakas's avatar
Vrykolakas
Frequent Visitor
3 years ago
Solved

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

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...
General Comment
Need Help
Show and Tell
  • v-xiaotang's avatar
    v-xiaotang
    3 years ago

    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.

v-xiaotang's avatar
v-xiaotang
Community Support
3 years ago

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.