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 create a subquery and populate new columns to my original database with the previous year's data.
Since I couldn't my query to recursively search itself on like a sumifs would on Excel, I've tried the same approach I would take on SQL by duplicating my database and using Table.SelectRows to search my duplicate database for the previous year's sales data like this:
PS: PYDATE is a column with the previous year's date it should filter, for example: Current Month: "SEP22" PYDATE will contain "SEP21" for that row.
Table.AddColumn(#"Renamed Columns1", "Sales (Q) PY", each List.Sum(Table.SelectRows(
NestedTable[Country] = [Country] and
NestedTable[DATE] = [PYDATE] and
NestedTable[Sales_Segment] = [Sales_Segment] and
NestedTable[ProductID] = [ProductID] and
NestedTable[Location] = [Location]
Now the problem I'm having is that my solution works, but it takes forever to run since I have a large dataset.
I imagine having it run on a measure on power bi will be even slower than my current approach.
Is there a more optimal way for calculating this data ?
|COUNTRY|| | DATE|| | SALES_SEGMENT|| | PRODUCT_ID|| | LOCATION|| | SALES(QTY)|| | PYDATE|| | PY_SALES(QTY)|
|AB|| | OCT22|| | D|| | A1|| | ABC|| | 100|| | OCT21|| | ?|
|AB|| | OCT21|| | D|| | A1|| | ABC|| | 50|| | OCT20|| | ?|
|CD|| | SEP22|| | O|| | B2|| | DEF|| | 90|| | SEP21|| | ?|