DAX Creating Virtual table with columns computed from multiple fact tables

[razmochaev]

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 ID	Sale Date	Previous Sale Date
1	10.05.2020
2	22.04.2020	19.03.2020
2	09.05.2020	22.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!

[AlB]

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 

[razmochaev]

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

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.

[AlB]

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 

[razmochaev]

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

[AlB · 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 

[PBIDevFW]

This is not the answer. He clearly said he needed a virtual table. Therefore, the solutions should be based on his use case. In enterprise environments with live connect, you can't create physical tables. Probably why he wants a virtual table, so solutions should be based on virtual, not physical, tables.

[razmochaev]

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