Forum Discussion
Optimize Query with data calculations
I have a database which is grouped by sales data per location, month, product_id and sales_segment. The thing is I want to compare previous year's sales data, where normally using SQL I would creat...
Hi Otavio_Hurtado - I think that DAX can handle this much better than SQL and Power Query. But how large is your Datastet?
To acheive this you need to create the following Table:
| COUNTRY | DATE | SALES_SEGMENT | PRODUCT_ID | LOCATION | SALES(QTY) |
| AB | 01/10/2022 | D | A1 | ABC | 100.00 |
| AB | 01/10/2022 | D | A1 | ABC | 50.00 |
| CD | 01/09/2022 | O | B2 | DEF | 90.00 |
| AB | 01/10/2021 | D | A1 | ABC | ?? |
| AB | 01/10/2021 | D | A1 | ABC | ?? |
| CD | 01/09/2021 | O | B2 | DEF | ?? |
The trick is to replace the Month with Start of Month date. This will allow you add a Calendar Table and then use the Time-Intelligence functions in DAX. This will allow you to avoid the expensive Joins required by SQL and Power Query.
About 24Million rows, I was also recommended that approach by a colleague, will try that.