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.

Frequent Visitor

## 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 "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..

1 ACCEPTED SOLUTION
Community Support

``````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.

Community Support

``````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.

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors