Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Calculating total for Fiscal Quarter

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's avatar
    Anonymous
    7 years ago
    -- 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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago
    -- 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