Calculating total for Fiscal Quarter

[Anonymous]

Hi Everyone, 

I have Date table which has a relationship with goals table. In both tables I have Fiscal Quarter numbers and fiscal years. In goals table I don't have a date column. I have a slicer on my report and I'm trying to create a measure which calculates sum of the fiscal quarter, but I need the total sum for that quarter that I picked from the date slicer. Currently, my measure is only showing the total when I pick the last month of that quarter. So, if I pick the first month of the quarter, I want to see the total sum for that whole quarter not just for that first month of that quarter. I don't want it cumulative I want the total. I've tried many different ways but I just couldn't do it. 

Here is the latest measure I've tried. Just to give you an idea.

CALCULATE(SUM('Goals'[Goal_Units]),FILTER('Goals','Goals'[FISCAL_YEAR_NUM] = MAX('Goals'[FISCAL_YEAR_NUM])
&&
'Goals'[FISCAL_QUARTER_NUM] = (CALCULATE(MAX('Goals'[FISCAL_QUARTER_NUM]), FILTER('Goals','Goals'[FISCAL_YEAR_NUM]=MAX('Goals'[FISCAL_YEAR_NUM]))))

))

Any help is appreciated. Thanks!

[Anonymous · Accepted solution]

-- First, you should create a base measure:

[Goal Units] = SUM ( 'Goals'[Goal_Units] ) 

[Goal Units for Quarter] =
-- There must be one quarter in scope for this to return values.
var __visibleFiscalYearNum = SELECTEDVALUE( 'Goals'[FISCAL_YEAR_NUM] )
var __visibleFiscalQuarterNum = SELECTEDVALUE( 'Goals'[FISCAL_QUARTER_NUM] ) 
var __result = 
	CALCULATE (
	    [Goal Units],
	    'Goals'[FISCAL_YEAR_NUM] = __fiscalYearNum,
	    'Goals'[FISCAL_QUARTER_NUM] = __fiscalQuarterNum,
            ALL ( Dates ) -- if the table filters Goals, then you have to take off all filters
	)
return
	__result

Best

Darek