cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## date table

Hi all,

I have two queries that i would to join...

So first query is a table of 3 columns (Date, product, Quantity) and for this one i have everyday of a year

for the second query, i have 4 columns (Date, product, price, segment product) but only 4 point of data per year per product (it's a pricing policy/strtegy)

my objective is to add the price columns to my first table, to be able to simply multiply my quantity by princing decided by corporate.

Thanks a lot

Let me know if i can be more precise

1 ACCEPTED SOLUTION
Community Support

Hi @gpp007 ,

First create a column in query 2:

``````next period =
var _next= CALCULATE(MIN('Table'[Date]),FILTER('Table','Table'[Date]>EARLIER('Table'[Date])&&'Table'[Product]=EARLIER('Table'[Product])))
Return
IF(_next=BLANK(),DATE(YEAR(MAX('Table'[Date])),12,31),_next)``````

Then create a measure as below:

``````_cost =
CALCULATE(MIN('Table'[Price]),FILTER('Table','Table'[Date]<=MAX('Query 1'[Date])&&'Table'[next period]>=MAX('Query 1'[Date])&&'Table'[Product]=MAX('Query 1'[Product])))
``````

And you will find that the price has been added to query 1 according to period setting.

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

3 REPLIES 3
Super User

@gpp007 , a new column like

minx(filter(table2, table1[product] =table2[product] && table1[Date] = table2[Date]),table2[price])

or

minx(filter(table2, table1[product] =table2[product] && year(table1[Date]) = year(table2[Date])),table2[price])

New Member

hi amitchandak

thanks for quick reply, i used your method, but it partially worked... the difficulty is to have a changing price between period, if i use your method, on my new column i only have the data i need at one date vs all date (in order to calculate turnover etc).

Community Support

Hi @gpp007 ,

First create a column in query 2:

``````next period =
var _next= CALCULATE(MIN('Table'[Date]),FILTER('Table','Table'[Date]>EARLIER('Table'[Date])&&'Table'[Product]=EARLIER('Table'[Product])))
Return
IF(_next=BLANK(),DATE(YEAR(MAX('Table'[Date])),12,31),_next)``````

Then create a measure as below:

``````_cost =
CALCULATE(MIN('Table'[Price]),FILTER('Table','Table'[Date]<=MAX('Query 1'[Date])&&'Table'[next period]>=MAX('Query 1'[Date])&&'Table'[Product]=MAX('Query 1'[Product])))
``````

And you will find that the price has been added to query 1 according to period setting.

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors