Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
v-jiascu-msft
Employee
Employee

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors