Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I've created a calculation group (Last 3/6/12 months) that I'm using as a slicer in my report. This is working great until I want to show a cumulative calculation. I've tried multiple scenarios, excluding measure from calculation group, using ALL vs ALLSELECTED in measure and all product incorrect results.
Calculation Group Item ( this is for the Last 12 months, but have the same for 3 & 6 months)
VAR _max =
EDATE(
CALCULATE(
MAX( 'Accounting Period'[Start Date] ),
FILTER( ALL( 'Accounting Period' ), 'Accounting Period'[Is Current Month] = 1 )
),
1
VAR _type = -12
VAR _min = DATE( YEAR( _max ), MONTH( _max ) + _type, 1 )
RETURN
IF(
SELECTEDMEASURENAME( ) = "Net New Deals",
CALCULATE(
SELECTEDMEASURE( ),
FILTER('Accounting Period', 'Accounting Period'[Start Date] < _max && 'Accounting Period'[Start Date] >= _min ),
USERELATIONSHIP( 'Accounting Period'[AccountingPeriodKey], Opportunity[CreatedDateAccountingPeriodKey] )
),
CALCULATE(
SELECTEDMEASURE( ),
FILTER( 'Accounting Period', 'Accounting Period'[Start Date] < _max && 'Accounting Period'[Start Date] >= _min )
)
)
Measures:
Net New Deals = CALCULATE(DISTINCTCOUNTNOBLANK(Opportunity[Id]),Opportunity[Type]="New Business")
Cumulative Net New Deals =
var maxAccountingPeriodKey = CALCULATE(max('Accounting Period'[AccountingPeriodKey]),ALLSELECTED('Accounting Period'))
return CALCULATE([Net New Deals],Filter(ALL('Accounting Period'),'Accounting Period'[AccountingPeriodKey]<=maxAccountingPeriodKey),USERELATIONSHIP('Accounting Period'[AccountingPeriodKey],Opportunity[CreatedDateAccountingPeriodKey]))
The above returns the same value for cumulative:
I've also tried the following options with results notated:
Replaced ALLSELECTED with ALL in Cumulative Net New Deals, which returns
Solved! Go to Solution.
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
....
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.
User | Count |
---|---|
77 | |
74 | |
63 | |
61 | |
45 |
User | Count |
---|---|
108 | |
102 | |
93 | |
83 | |
64 |