Please Help - Adding the Three Monthly Averages for a Quarter

[ClaireBear]

Hello, 

I have created a measure which works out the average value for the month and a measure which works out the average for the quarter.  These are displayed on a card visual. 

I have now been asked to add each month average value within the quarter to get a total. 

So, if the average values are, Oct $200, Nov, $400 and December $150, I need the card to read $750 and not average of the three values combined (which it is currently doing)

Measures:

Previous Month Basket Value =

CALCULATE([Average Basket Value],DATESMTD(ENDOFMONTH(dateadd('Dimension Time'[Date],-1,MONTH))))

Previous Quarter Basket Value =

CALCULATE([Average Basket Value],DATESQTD(ENDOFQUARTER(dateadd('Dimension Time'[Date],-1,QUARTER))))

Is anyone able to help with this weird senario? 

 

[ClaireBear]

Thank you so much @amitchandak , this works perfectly!

[amitchandak]

@ClaireBear , Try like

Previous Quarter Basket Value =
CALCULATE(sum(values('Dimension Time'[Month Year]),[Average Basket Value]),DATESQTD(ENDOFQUARTER(dateadd('Dimension Time'[Date],-1,QUARTER))))

[ClaireBear]

Hi @amitchandak , Thank you for your response. The measure is showing an error from the (values('Dimension Time'[Month Year]), part of the calculation. 

[amitchandak · Accepted solution]

@ClaireBear , My bad, it needs to be sumx, you also need month year as column

Previous Quarter Basket Value =
CALCULATE(sumX(values('Dimension Time'[Month Year]),[Average Basket Value]),DATESQTD(ENDOFQUARTER(dateadd('Dimension Time'[Date],-1,QUARTER))))