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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
LanceTDL
Frequent Visitor

Create Table, or Measure for Current Price by Dates

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:

ItemPrice
A30
B20
C60


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:

DateItemPrice
June, 22, 2021A30
June, 22, 2021B20
June, 22, 2021C60
June, 23, 2021A30
June, 23, 2021B20
June, 23, 2021C60



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

Thanks,

1 ACCEPTED SOLUTION
fhill
Resident Rockstar
Resident Rockstar

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

 

 

fhill_0-1626975488783.png

 

 




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to give back to the community!
Thank You!




View solution in original post

3 REPLIES 3
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
fhill
Resident Rockstar
Resident Rockstar

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

 

 

fhill_0-1626975488783.png

 

 




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to give back to the community!
Thank You!




Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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