Create Table, or Measure for Current Price by Dates

[LanceTDL]

Hello, 

I have a forcasting formula we built in Power Bi and it is working great for calculating the number of units expected to be sold, both in historical and future dates. Where I am having issues is calculating the forecasted number of units by the current price. For many items the price doesn't show up. The solution I have is to create a new table that contains every item with its current price for all the dates in my date table. For example:

Item Table:

Item	Price
A	30
B	20
C	60

Date Table Covers every day from 2019 until December 31st 2022 

To shorten the example for two days I would expect my new table to be:

Date	Item	Price
June, 22, 2021	A	30
June, 22, 2021	B	20
June, 22, 2021	C	60
June, 23, 2021	A	30
June, 23, 2021	B	20
June, 23, 2021	C	60

How do I build this date, item and price table?

Thanks,

[Ashish_Mathur]

Hi,

Assuming you have your Calendar Table built in the Query Editor, go to the Items Table in the Query Editor > Add Column > Custom Column and write this formula

=Calendar[Date]

Hope this helps.

[v-shex-msft]

Hi @LanceTDL,

If you want to expand table records with a specific category, you can try to use CROSSJOIN functions and it will return a repeating table that includes records from two parameter tables.

Power BI DAX #1: The Magic of CROSSJOIN | by Michael Olafusi | Medium
Regards,

Xiaoxin Sheng

[fhill · Accepted solution]

My Power BI gut says, what you are trying to do can be more efficently done with proper joining, or some code magic... but here's what you need for now.  Hope it works!

Table 2 = CROSSJOIN('Date Table', 'Item Table')