cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted
Super User IX
Super User IX

Re: Measure based on multiple criteria including date

@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] )



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

3 REPLIES 3
Highlighted
Regular Visitor

Re: Measure based on multiple criteria including date

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

Highlighted
Super User IX
Super User IX

Re: Measure based on multiple criteria including date

@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] )



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Highlighted
Regular Visitor

Re: Measure based on multiple criteria including date

Amit,

 

Thank you, as simple as that.

 

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors