Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 ...
Need Help
Tips and Tricks
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi Anonymous ,

     

    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!

     

amitchandak's avatar
amitchandak
Super User
5 years ago

Anonymous , 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])

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

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

    • v-kelly-msft's avatar
      v-kelly-msft
      Community Support
      5 years ago

      Hi Anonymous ,

       

      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!