Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Cumulative measure with calculation group not correct

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,...
  • Anonymous's avatar
    Anonymous
    3 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.