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

     

     

     

     

jdbuchanan71's avatar
jdbuchanan71
Super User
4 years ago

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'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
        Super 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
	)