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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
BasB
Regular Visitor

Measure based on multiple criteria including date

I would like to define measure Base_price: showing the price with Price_type = Base of a specific product at a certain date.

 

There are two tables:

a Transaction table:

Date

Product_ID

Volume

29-Mar

D

45

23-Apr

D

56

19-Dec

D

55

 

a Price table:

Price_type

Product_ID

Price

Date_valid_from

Base

A

12

01-Feb

Base

A

13

04-Apr

Base

D

27

02-Dec

Base

C

33

23-May

Gross

A

14

01-Feb

Base

E

2

13-Mar

Gross

E

3

27-Mar

Gross

G

88

04-Jul

Net

D

27.5

01-Feb

Net

D

28

13-Mar

Net

E

1.8

27-Mar

Net

G

40

07-Apr

 

  • The Date_valid_from identifies the start of a price and a price could change when a later Date_valid_from exists. This implies the measure should find the Price that is valid at the date of the transaction.
  • In the Price table more type of Prices exists and the measure should find those of the Price_type “Base”
  • And finally the price should be related to the specific Product_ID.

In the data model there is no relationship defined between the two tables.

 

 

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@BasB , A new column in Transaction Table

price =
var _dt = maxx(filter(Price , Price[Product_ID] =Transaction[Product_ID] && Price[Date_valid_from] < Transaction[Date] && Price[Price_type] ="Base"),Price[Date_valid_from] )
return
maxx(filter(Price , Price[Product_ID] =Transaction[Product_ID] && Price[Date_valid_from] = _dt && Price[Price_type] ="Base"),Price[Price] )

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@BasB , A new column in Transaction Table

price =
var _dt = maxx(filter(Price , Price[Product_ID] =Transaction[Product_ID] && Price[Date_valid_from] < Transaction[Date] && Price[Price_type] ="Base"),Price[Date_valid_from] )
return
maxx(filter(Price , Price[Product_ID] =Transaction[Product_ID] && Price[Date_valid_from] = _dt && Price[Price_type] ="Base"),Price[Price] )

Amit,

 

Thank you, as simple as that.

 

BasB
Regular Visitor

Transaction table with more records:

Date

Product_ID

Volume

26-Mar

E

9

29-Mar

A

2

29-Mar

D

45

04-Apr

A

18

23-Apr

A

3

23-Apr

D

56

12-Nov

C

33

04-Dec

A

123

19-Dec

D

55

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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