Forum Discussion

fullcount's avatar
fullcount
Frequent Visitor
4 years ago
Solved

Optimizing an Average measure

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 ...
Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    4 years ago

    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's avatar
fullcount
Frequent Visitor
4 years ago

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's avatar
fullcount
Frequent Visitor
4 years ago

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's avatar
    jdbuchanan71
    Icon for Super User rankSuper User
    4 years ago

    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
	)