Hello, I’m asking for help. The measure below takes a very long time to calculate. I have tried many optimization methods, but unfortunately, I can’t seem to do it well. Could I ask you for some tips on how to improve this?

Without having the data / some sample data, a few initial questions appear to my mind:

why do you need the Summarize function, is aggregation not possible in the visual ?

in the Summarize, only [StoreID] is added, have you tried to visualise your temp-table as a calculated table to see the prelimanary outcome, to see if it does what you expect ? What is the outcome in the calculated table of “measure ACT” and “measure LY” ? Assuming only a short table with all the stores with two columns with unit-quantities will appear

why is DWH FactL4L[Units] not included in the Summarize? If the Summarize is needed, standaard sum- and Last Year-calculations should be possible with summarized DWH FactL4L[Units]

why is a date-filter needed on the summarized DWH FactL4L table, selecting the DimDate_ID bigger than the minimum date in the table and smaller than the maximum date of the table ?

why is SUMX needed in your DIVIDE formula ?

Could BLANK() in your Divide formula not being sufficient if last year data is NOT available, and therefore ‘DWH FactL4L’[L4L_Monthly] = TRUE () is NOT needed as a filter at all (guessing this condition has to do with this) ?

How are the measures [measure ACT] and [measure LY] calculated ?