Good afternoon, I have the following problem.
I have the following data model
The Price List table is a table with approximately 12 million records, since they are sku codes and dates, while the SKC IPC table has a subset of codes that want to evaluate the evolution of prices over time.
For this I made the following table (as a visualization)
The last 2 forimulas what they are looking for is the variation of the column Price LP respcto of the previous one, is constructed in the following way:
Variation ($) Prices =
where _pre=
CALCULATE(
.MAX('Price Lists'[fecha_creacion_lp]),
FILTER(ALL('Price Lists'),
'Price Lists'[Key 1]=MIN('Price Lists'[Key 1]) &&
'Price Lists'[fecha_creacion_lp]<MIN('Price Lists'[fecha_creacion_lp])
)
)
where _preRES=
CALCULATE(
.MAX('Price Lists'[LP Price]),
FILTER(ALL('Price Lists'),
'Price Lists'[Key 1]=MIN('Price Lists'[Key 1]) &&
'Price Lists'[fecha_creacion_lp]=_pre))
return
IF(
ISBLANK( _preRES),
BLANK(),
MIN('Price Lists'[LP Price])-_preRES)
and the second is percentage
Variation (%) Prices =
where _pre=
CALCULATE(
.MAX('Price Lists'[fecha_creacion_lp]),
FILTER(ALL('Price Lists'),
'Price Lists'[Key 1] =MIN('Price Lists'[Key 1]) &&
'Price Lists'[fecha_creacion_lp]<MIN('Price Lists'[fecha_creacion_lp])
)
)
where _preRES=
CALCULATE(
.MAX('Price Lists'[LP Price]),
FILTER(
ALL('Price Lists'),'Price Lists'[Key 1]=MIN('Price Lists'[Key 1]) &&
'Price Lists'[fecha_creacion_lp]=_pre))
return
IF(
ISBLANK( _preRES),
BLANK(),
DIVIDE( MIN('Price Lists'[LP Price]),_preRES)-1)
The results are correct, as seen in the resulting table, however when adding these two 2 measures the performance is significantly slower, either when filtering something, when expanding or contracting the visualization etc. and the second problem I present is that when you collapse the table, the results are wrong.
Then I would like help in 2 topics (sorted by priority)
1.- Improve the performance of the measure created
2.- Correct the measure so that when grouping / contracting the visualization correctly calculates the variation in $ and %
From already thank you very much!
