Forum Discussion

mshashmi's avatar
mshashmi
Frequent Visitor
9 years ago
Solved

Moving Average changes when using a Slicer

Hi   I have a table and a measure is present to calculate moving average. I have a date slicer present too. Moving average of mine is for previous 3 weeks. So whn any date range is selected, the da...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago
    Moving Average= CALCULATE(
	AVERAGEX(
		lineDataClosed,
		SUM(lineDataClosed[Closed Count])/3
	),
	ALL(lineDataClosed),
	DATESINPERIOD(
		lineDataClosed[Week],
		LASTDATE(lineDataClosed[Week]),
		-21,
		DAY
	)
)

    Try this and let me know the result.

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Have a look at the DAX statement you have used to calculate your measures.  Dax Statements are context sensitive, meaning that they change their results based on slicers.

 

You might need to make use of an ALL() style function to tell it to consider all of the information.  Its going to be tough to give you a more direct solution because ALL() will avoid every slicer, which you might not want.  So you might need to use ALLEXCEPT().

 

 

  • mshashmi's avatar
    mshashmi
    Frequent Visitor
    9 years ago

    Anonymous

     

    Moving Average= CALCULATE(AVERAGEX(lineDataClosed,sum(lineDataClosed[Closed Count])/3),DATESINPERIOD(lineDataClosed[Week],LASTDATE(lineDataClosed[Week]),-21,DAY))

     

    This expression is giving me the result as per now. Where do I need to place All() or ALLEXCEPT() to get the desired result?

     

    Thanks

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago
      Moving Average= CALCULATE(
	AVERAGEX(
		lineDataClosed,
		SUM(lineDataClosed[Closed Count])/3
	),
	ALL(lineDataClosed),
	DATESINPERIOD(
		lineDataClosed[Week],
		LASTDATE(lineDataClosed[Week]),
		-21,
		DAY
	)
)

      Try this and let me know the result.

      • mshashmi's avatar
        mshashmi
        Frequent Visitor
        9 years ago

        Anonymous

         

        Thanks a lot