Optimizing an Average measure

[fullcount]

Hello,

I have created a measure that averages weekly item placements over the previous six weeks.  (Because the same item may be placed in the same store each week, I need to capture this at the weekly level and then create an average of weekly figures so that these are not counted twice.)  The measure works, it's just very slow.  Could you offer any advice on how to optimize this?  One note: I am using a measure for Placements right now, but could switch to a column if that enables other methods.

Six WK Avg Weekly Placements = DIVIDE(

CALCULATE([Placements],DATESINPERIOD(Calendar_Lookup[Date],MAX(Sales[Date]),-7,DAY))+

CALCULATE([Placements],DATESINPERIOD(Calendar_Lookup[Date],MAX(Sales[Date])-7,-7,DAY))+

CALCULATE([Placements],DATESINPERIOD(Calendar_Lookup[Date],MAX(Sales[Date])-14,-7,DAY))+

CALCULATE([Placements],DATESINPERIOD(Calendar_Lookup[Date],MAX(Sales[Date])-21,-7,DAY))+

CALCULATE([Placements],DATESINPERIOD(Calendar_Lookup[Date],MAX(Sales[Date])-28,-7,DAY))+

CALCULATE([Placements],DATESINPERIOD(Calendar_Lookup[Date],MAX(Sales[Date])-35,-7,DAY)),6,0)

Thanks!

[jdbuchanan71]

That chart looks like you are comparing a trend line to the actual.  What if you put your placements and mine side by side in a table by date?

[fullcount]

I had also established the MaxDate as another variable and it was throwing things off, when I rewrote with my original six MAX(Calendar_Lookup[Date]) syntax, our numbers matched.  Thanks so much for your help!

[fullcount]

@jdbuchanan71 Can you see any reason why the CalcTable version you suggested would affect my ability to move the dates?  I'm trying to compare the last six weeks' weekly average with the weekly average from 46-51 weeks ago (LY forward rate).  When I use my old [Placements] measure, it will calculate the old range correctly, but using the new CalcTable version of the measure these two produce the same outcome:

LY Forward 6W Avg Weekly Placements:=

VAR TabPlacements = COUNTROWS(CALCULATETABLE(VALUES(Sales[Item Name + Store Name]),Sales[Units Sold]>0))

RETURN

DIVIDE(

CALCULATE(TabPlacements,DATESINPERIOD(Calendar_Lookup[Date],(MAX(Calendar_Lookup[Date])-358),-7,DAY))+

CALCULATE(TabPlacements,DATESINPERIOD(Calendar_Lookup[Date],(MAX(Calendar_Lookup[Date])-351),-7,DAY))+

CALCULATE(TabPlacements,DATESINPERIOD(Calendar_Lookup[Date],(MAX(Calendar_Lookup[Date])-344),-7,DAY))+

CALCULATE(TabPlacements,DATESINPERIOD(Calendar_Lookup[Date],(MAX(Calendar_Lookup[Date])-337),-7,DAY))+

CALCULATE(TabPlacements,DATESINPERIOD(Calendar_Lookup[Date],(MAX(Calendar_Lookup[Date])-330),-7,DAY))+

CALCULATE(TabPlacements,DATESINPERIOD(Calendar_Lookup[Date],(MAX(Calendar_Lookup[Date])-323),-7,DAY)),6,0)

TY Last 6W Avg Weekly Placements:=

VAR TabPlacements = COUNTROWS(CALCULATETABLE(VALUES(Sales[Item Name + Store Name]),Sales[Units Sold]>0))

RETURN

DIVIDE(

CALCULATE(TabPlacements,DATESINPERIOD(Calendar_Lookup[Date],MAX(Calendar_Lookup[Date]),-7,DAY))+

CALCULATE(TabPlacements,DATESINPERIOD(Calendar_Lookup[Date],MAX(Calendar_Lookup[Date])-7,-7,DAY))+

CALCULATE(TabPlacements,DATESINPERIOD(Calendar_Lookup[Date],MAX(Calendar_Lookup[Date])-14,-7,DAY))+

CALCULATE(TabPlacements,DATESINPERIOD(Calendar_Lookup[Date],MAX(Calendar_Lookup[Date])-21,-7,DAY))+

CALCULATE(TabPlacements,DATESINPERIOD(Calendar_Lookup[Date],MAX(Calendar_Lookup[Date])-28,-7,DAY))+

CALCULATE(TabPlacements,DATESINPERIOD(Calendar_Lookup[Date],MAX(Calendar_Lookup[Date])-35,-7,DAY)),6,0)

[jdbuchanan71]

@fullcount 

Make this into a measure rather than a variable.

TabPlacements = COUNTROWS(CALCULATETABLE(VALUES(Sales[Item Name + Store Name]),Sales[Units Sold]>0))

Then use the measure in your other calcs.

LY Forward 6W Avg Weekly Placements:=
DIVIDE(
		CALCULATE([TabPlacements],DATESINPERIOD(Calendar_Lookup[Date],(MAX(Calendar_Lookup[Date])-358),-7,DAY))+
		CALCULATE([TabPlacements],DATESINPERIOD(Calendar_Lookup[Date],(MAX(Calendar_Lookup[Date])-351),-7,DAY))+
		CALCULATE([TabPlacements],DATESINPERIOD(Calendar_Lookup[Date],(MAX(Calendar_Lookup[Date])-344),-7,DAY))+
		CALCULATE([TabPlacements],DATESINPERIOD(Calendar_Lookup[Date],(MAX(Calendar_Lookup[Date])-337),-7,DAY))+
		CALCULATE([TabPlacements],DATESINPERIOD(Calendar_Lookup[Date],(MAX(Calendar_Lookup[Date])-330),-7,DAY))+
		CALCULATE([TabPlacements],DATESINPERIOD(Calendar_Lookup[Date],(MAX(Calendar_Lookup[Date])-323),-7,DAY))
		,6,0
	)

[jdbuchanan71 · Accepted solution]

@fullcount 

Context transition might be jamming you up on the [Placements] measure.  In my testing, this returns the same results and is almost 3x faster.

Placements =
COUNTROWS(
    CALCULATETABLE(
        VALUES(Sales[Item Name + Store Name]),
        Sales[Units Sold] > 0
    )
)

This is your measure.

This is mine

[fullcount]

Thanks, @jdbuchanan71 .  I wasn't able to completely reproduce the [Placements] value with the CALCULATETABLE version.  Here are the results of the two measures on the same chart:

I have triple-checked the dates involved, so I'm thinking there's just a difference on what values the two measures reach.  Think it's worth me switching to a Column?

Thanks!

[fullcount]

Hi all,

Thanks for the notes so far.  [Placements] = 

CALCULATE(SUMX(VALUES(Sales[Item Name + Store Name]),1),Sales[Units Sold]>0); a column version would be Placements = IF(Sales[Units Sold]>0,1,0)

Thanks!

[jdbuchanan71]

@fullcount 

Can you give us the calculation for the [Placements] measure?  It's possible that this would give you the same result depending on what that measure is calculating.

Six WK Avg Weekly Placements =
DIVIDE (
    CALCULATE (
        [Placements],
        DATESINPERIOD ( Calendar_Lookup[Date], MAX ( Sales[Date] ), -42, DAY )
    ),
    6
)

[selimovd]

Hey @fullcount ,

that's a good question. It can have a few reasons why it's slow. I guess it would help to show or analyze the Measure [Placements] as you use it many times. Can you post the source code of that measure or better the file if possible?

What will definitely make it a little bit faster is to put the MAX(Sales[Date]) in a variable. Like that is has to be evaluated only once and not 6 times:

Six WK Avg Weekly Placements =
VAR vMaxDate = MAX(Sales[Date])
RETURN
DIVIDE(
    CALCULATE([Placements],DATESINPERIOD(Calendar_Lookup[Date],vMaxDate ,-7,DAY))+
    CALCULATE([Placements],DATESINPERIOD(Calendar_Lookup[Date],vMaxDate -7,-7,DAY))+
    CALCULATE([Placements],DATESINPERIOD(Calendar_Lookup[Date],vMaxDate -14,-7,DAY))+
    CALCULATE([Placements],DATESINPERIOD(Calendar_Lookup[Date],vMaxDate -21,-7,DAY))+
    CALCULATE([Placements],DATESINPERIOD(Calendar_Lookup[Date],vMaxDate -28,-7,DAY))+
    CALCULATE([Placements],DATESINPERIOD(Calendar_Lookup[Date],vMaxDate -35,-7,DAY)),
    6,
    0
)

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[jppv20]

Hi @fullcount ,

Could you provide some example data from the original table?

Jori