cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
apulic New Member
New Member

LY calculations slowing down performance of dashboards and reports

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?

1 REPLY 1
Microsoft v-jiascu-msft
Microsoft

Re: LY calculations slowing down performance of dashboards and reports

Hi @apulic,

 

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (3,798)