Forum Discussion
Cumulative measure with calculation group not correct
- Anonymous3 years ago
I ended up getting this to work by making the following changes:
Cumulative Net New Deals = var max_created_date = calculate(max(Opportunity[CreatedDateAccountingPeriodKey]),all(Opportunity)) return if(SELECTEDVALUE('Accounting Period'[AccountingPeriodKey])<=max_created_date,CALCULATE([Net New Deals], filter(ALLSELECTED('Accounting Period'), 'Accounting Period'[AccountingPeriodKey] <= max('Accounting Period'[AccountingPeriodKey]))))The change here was to get the max data value from the Opportunity instead of the Accounting Period table so that the result wasn't including periods that weren't selected in the slicer.
VAR _max = EDATE( CALCULATE( MAX( 'Accounting Period'[Start Date] ), FILTER( ALLSELECTED( 'Accounting Period' ), 'Accounting Period'[End Date] = EOMONTH( NOW( ), -1 ) ) ), 1 ) VAR _type = -6 VAR _min = DATE( YEAR( _max ), MONTH( _max ) + _type, 1 ) RETURN CALCULATE( SELECTEDMEASURE( ), FILTER( ALLNOBLANKROW( 'Accounting Period'[Start Date] ), 'Accounting Period'[Start Date] < _max && 'Accounting Period'[Start Date] >= _min ) )For the calculation group item, I was able to simplify and remove logic based on cumulative vs non-cumulative measure and it works for both scenarios.
Correct result should be:
Accounting Period | Net New Deals | Cumulative Net New Deals
Aug 2022 | 10 | 10
Sept 2022 | 11 | 21
Oct 2022 | 5 | 26
....
- Anonymous3 years agoNot applicable
I ended up getting this to work by making the following changes:
Cumulative Net New Deals = var max_created_date = calculate(max(Opportunity[CreatedDateAccountingPeriodKey]),all(Opportunity)) return if(SELECTEDVALUE('Accounting Period'[AccountingPeriodKey])<=max_created_date,CALCULATE([Net New Deals], filter(ALLSELECTED('Accounting Period'), 'Accounting Period'[AccountingPeriodKey] <= max('Accounting Period'[AccountingPeriodKey]))))The change here was to get the max data value from the Opportunity instead of the Accounting Period table so that the result wasn't including periods that weren't selected in the slicer.
VAR _max = EDATE( CALCULATE( MAX( 'Accounting Period'[Start Date] ), FILTER( ALLSELECTED( 'Accounting Period' ), 'Accounting Period'[End Date] = EOMONTH( NOW( ), -1 ) ) ), 1 ) VAR _type = -6 VAR _min = DATE( YEAR( _max ), MONTH( _max ) + _type, 1 ) RETURN CALCULATE( SELECTEDMEASURE( ), FILTER( ALLNOBLANKROW( 'Accounting Period'[Start Date] ), 'Accounting Period'[Start Date] < _max && 'Accounting Period'[Start Date] >= _min ) )For the calculation group item, I was able to simplify and remove logic based on cumulative vs non-cumulative measure and it works for both scenarios.