cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
razmochaev
Helper I
Helper I

DAX Creating Virtual table with columns computed from multiple fact tables

Hi everyone,

I'm struggling with the following problem: I have two fact tables with sales data and several dimensions tables (Products and Dates).

I need to create a virtual table with the following columns:

 

Product IDSale DatePrevious Sale Date
110.05.2020 
222.04.202019.03.2020
209.05.202022.04.2020


The problem is that I have two fact tables from which the columns must be computed.

 

If anyone can provide a possible solution, or hint, or link to a thread with the same problem, I would be really grateful!

1 ACCEPTED SOLUTION

@razmochaev 

I think it would be best to create not a virtual but a physical table that combines the two tables you now have. It will save you a lot of problems and time, I believe.

If you do want to find the previous date in the virtual table, you can do something like

MAXX(FILTER(VirtualTable, [Date_] < currentdate_),[Date_])

Please mark the question solved when done and consider giving kudos if posts are helpful.

 Cheers 

SU18_powerbi_badge

View solution in original post

6 REPLIES 6
AlB
Super User
Super User

Hi @razmochaev 

Can you show the structure of the tables needed to generate the result table? And explain how exactly the information in the result table should be obtained?

Please mark the question solved when done and consider giving kudos if posts are helpful.

 

 Cheers 

 

SU18_powerbi_badge

I'll give a simplified table structured (originally they are in Russian :-)). The point is that I cannot append the in Power query.

Screenshot_1.png

The resulting table I am trying to create must contain, for each product ID and its sales dates, the date of previous sale for this product for a given date.

 

How about using UNION() to get a table that has all the rows of the other two (just like an append in M) and then work with that table

Please mark the question solved when done and consider giving kudos if posts are helpful.

 Cheers 

SU18_powerbi_badge

But how can I compute the "Previous Sale Date" column of this virtual table. I cannot use CALCULATE(MAX(UnionTable[Sale date]),...) since I cannot use Virtual table's columns as a column reference...

@razmochaev 

I think it would be best to create not a virtual but a physical table that combines the two tables you now have. It will save you a lot of problems and time, I believe.

If you do want to find the previous date in the virtual table, you can do something like

MAXX(FILTER(VirtualTable, [Date_] < currentdate_),[Date_])

Please mark the question solved when done and consider giving kudos if posts are helpful.

 Cheers 

SU18_powerbi_badge

Thank you for the advice! I guess it'll be easier.

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors