Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a report where I calculate the stock backwards (and projected forwards), for this I use a Cross join between my items table and calendar table.
However, it is quite a lot of data.. three years of history is needed and ~16000 articles, so in total 16 million rows..
When I do the cross join in PowerBI the performance in PowerBI desktop becomes really slow, If I want to add a new column, I have to wait ~30 seconds before typing and then 30-60 before I see the output..
Would it be smarter to do the cross join in the import? Of course the load will take much longer.
Or are there any other ways?
I need the Cartesian product unfortunately..
Hi,
No not really..
I use a table with the Cartesian product of all dates and all items.
Then I link this to my transactions table (all movements, PO inbound, SO outbound, stock correction, etc.) Plus my forecast transcation tabel
Based on this, I calculate what the stock was each day and what stock level I have estimated it to be each day going forward.
@vipett , if you are doing this because you need to have data between the start date and the end date , refer
https://amitchandak.mediumcom/dax-get-all-dates-between-the-start-and-end-date-8f3dac4ff90b
https://amitchandak.medium.com/power-query-get-all-dates-between-the-start-and-end-date-9ad6a84cf5f2
You can generate function
even measures can do
Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM
https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...
User | Count |
---|---|
103 | |
90 | |
78 | |
72 | |
68 |
User | Count |
---|---|
108 | |
96 | |
95 | |
74 | |
71 |