Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to Solution.
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
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
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.
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
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...
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
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.
Thank you for the advice! I guess it'll be easier.
User | Count |
---|---|
60 | |
22 | |
18 | |
18 | |
16 |
User | Count |
---|---|
86 | |
54 | |
54 | |
38 | |
21 |