LY calculations slowing down performance of dashboards and reports

[Anonymous]

Hi,

Recently I realized that my calculations for last year data slow down the performance of dashboards and reports (it takes a long time to load dashboards and reports).

Example of LY calculation:

Net Sales LY (CY/LY Valid Sales) =

VAR CompStatus1 = IF(CALCULATE(HASONEVALUE('Store Status Daily'[Comp Status]), ALL( 'Store Status Daily'[StoreDateKey])),VALUES('Store Status Daily'[Comp Status]),BLANK())

VAR CompStatus2 = IF(CALCULATE(HASONEVALUE('Store Status Daily'[Comp Status Name]), ALL( 'Store Status Daily'[StoreDateKey])), VALUES('Store Status Daily'[Comp Status Name]), BLANK())

RETURN

CALCULATE(

SUM(Sales[Net Sales Amount]),

ALL('Store Status Daily'[Comp Status]),

ALL('Store Status Daily'[Comp Status Name]), 'Store Status Daily'[Sales Store Day Count NY]=1,'Store Status Daily'[Sales Store Day Count]=1,

DATEADD('Fiscal Calendar'[Calendar Date], -364, DAY),

IF( CompStatus1 = BLANK(),'Store Status Daily'[Comp Status NY] IN {"Comp", "Non-Comp"} ,'Store Status Daily'[Comp Status NY] = CompStatus1),

IF( CompStatus2 = BLANK(), 'Store Status Daily'[Comp Status Name NY] IN {"Closed", "Comp", "Non-Comp", "NRO"}, 'Store Status Daily'[Comp Status Name NY] = CompStatus2))

In this example I want to calculate last year’s Net Sales on a given date for a given store. I’m including only those store/date combinations that had sales for that date and those that were not closed (that’s why Sales Store Day Count = 1) and also those stores that have sales and are not closed for corresponding date this year (that’s why Sales Store Day Count NY = 1). Last year’s date is calculated as date – 364 days.

Store can be Comp or Non-Comp.

Comp Status – either “Comp” (store opened for more than 517 days) or “Non-Comp” (Store opened in the range of 366 to 517 days)

Comp Status Name – “Comp”, “Non-Comp”, “Closed”, “NRO”

Sales table and Store Status Daily table have one to many relationship based on StoreDateKey.

Is there any other (simpler) way to calculate LY values with logic described?

[v-jiascu-msft]

Hi @Anonymous,

Can you share a sample file, please? Please mask the sensitive parts first.

It's hard to find the logic without the data structure. Maybe the two VAR variables can be optimized like below.

VAR CompStatus1 =
    selectedvalue( 'Store Status Daily'[Comp Status] )
VAR CompStatus2 =
    selectedvalue( 'Store Status Daily'[Comp Status Name] )

Best Regards,
Dale