Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Draw average line in Line and clustured column chart

Hello, I'm trying to draw an average line of a certain period of time but i cant get it to work, i would like a line like this 

 

But instead I'm getting something like this

the idea is that this line represents the average amount of something in a 2 month range, but for some reason its showing that line, to get the average im using this formula (which im trying to represent in the chart)

 

FILTER(TEST) = 
VAR PERIODOMINIMO = CALCULATE(MAX(CALENDARIO[PERIODO]), DATEADD((CALENDARIO[Date]),-2,MONTH))
VAR PERIODOMAXIMO = CALCULATE(MAX(CALENDARIO[PERIODO]))
VAR PROM = AVERAGE(DatosWebCenter[Round])
RETURN
CALCULATE(PROM,KEEPFILTERS(DatosWebCenter[PERIODO] >= PERIODOMINIMO && DatosWebCenter[PERIODO] < PERIODOMAXIMO), ALL(CALENDARIO))

 

Could anybody please give me a hand? 

Thanks!

Need Help
Tips and Tricks
  • littlemojopuppy's avatar
    littlemojopuppy
    4 years ago

    HI Anonymous 

     

    Try this...

    VAR	TwoMonthDates =
	CALCULATETABLE(
		DATESINPERIOD(
			DateTable[Date],
			MAX(DateTable[Date]),
			-2,
			MONTH
		),
		REMOVEFILTERS(DateTable[Date])
	)
RETURN

CALCULATE(
	[PROM],
	TwoMonthDates
)

    This requires a table table and that it's marked appropriately.  Hope this helps!

9 Replies

  • littlemojopuppy's avatar
    littlemojopuppy
    Icon for Community Champion rankCommunity Champion
    4 years ago

    HI Anonymous 

     

    Try this...

    VAR	TwoMonthDates =
	CALCULATETABLE(
		DATESINPERIOD(
			DateTable[Date],
			MAX(DateTable[Date]),
			-2,
			MONTH
		),
		REMOVEFILTERS(DateTable[Date])
	)
RETURN

CALCULATE(
	[PROM],
	TwoMonthDates
)

    This requires a table table and that it's marked appropriately.  Hope this helps!

    • littlemojopuppy's avatar
      littlemojopuppy
      Icon for Community Champion rankCommunity Champion
      4 years ago

      FYI: I typed that in Notepad so it might not be syntactically perfect

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Its perfect! Just that its not working for me 😞

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hello! Thanks for the reply! 

      You mean to type something like this?

      FILTER(TEST) = 
VAR PROM = AVERAGE(DatosWebCenter[Round])
VAR	TwoMonthDates =
	CALCULATETABLE(
		DATESINPERIOD(
			CALENDARIO[Date],
			MAX(CALENDARIO[Date]),
			-2,
			MONTH
		),
		REMOVEFILTERS(CALENDARIO[Date])
	)
RETURN

CALCULATE(
	PROM,
	TwoMonthDates
)

      Because if so I only get a single Dot valued on 0 😞 

       

      • littlemojopuppy's avatar
        littlemojopuppy
        Icon for Community Champion rankCommunity Champion
        4 years ago

        Anonymous that's because you're calculating the average on a single week. Take AVERAGE(DatosWebCenter[Round]) and move it into the CALCULATE statement.