Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
-- 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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
20 | |
18 | |
16 | |
15 |
User | Count |
---|---|
49 | |
26 | |
22 | |
17 | |
16 |