Forum Discussion

n8ball's avatar
n8ball
Advocate I
3 years ago

Calling DAX Masters - Price Realization Over Time

I've been working on this measure for a long time. Trying to find the best performance. Currently it is not good once you start filtering by Months. Lots of nested iterators and high materialization....
puneetvijwani's avatar
puneetvijwani
Resolver IV
3 years ago

n8ball  Will Suggest you to change the model and try  Pre loading the data as AggregatedSales table by either on date or by month level granularity
For eg below 


AggregatedSales = SUMMARIZE( FILTER('Sales', 'Sales'[ReturnAmount] = 0), 'Product'[ProductKey], 'Channel'[Channel], 'Calendar'[DateKey], "SalesAmount", SUM('Sales'[SalesAmount]), "SalesQuantity", SUM('Sales'[SalesQuantity]) )

and then create DAX 


or you can materlize and filter your table which you are doing as pre calculation step  in cleansing process and loadit as an Aggregate table , It shall reduce lots of complexity 

Foy dynamic time intelligence you can use something as below article by phil seamark

https://dax.tips/2019/10/09/row-based-time-intelligence/


------------------------------------------------------------------------------------------

Linkedn :-https://www.linkedin.com/in/puneet-v-376470135/
Youtube :-https://www.youtube.com/@datatrends101

 

n8ball's avatar
n8ball
Advocate I
3 years ago

Yup. I will have to try to generate a price table. The only issue there is you loose the dynamic nature of the measure. I think we would need to wrap the SUMs you have above in a CALCULATE to perform context transition to get the right result. Also would need do YTD and PYTD calc for each date.