Forum Discussion

MagikJukas's avatar
MagikJukas
Icon for Resolver III rankResolver III
4 years ago

Rolling sum excluding filtered values

I have this beautiful formula giving me the rolling 28 days sum. Rolling28 = CALCULATE ( SUM ( 'Order Entry'[Sales] ), FILTER( ALLSELECTED('Order Entry'), 'Order Entry'[Created on]>=MAX(('Order ...
MagikJukas's avatar
MagikJukas
Icon for Resolver III rankResolver III
4 years ago

Thanks for your comment:

Without Allselected, the chart becomes like this:

Your formula gives the same result.

daXtreme's avatar
daXtreme
Icon for Solution Sage rankSolution Sage
4 years ago

Perhaps this?

[Sales] = SUM ( 'Order Entry'[Sales] )

[Rolling28 Sales] =
var CurrentCreatedOn = MAX( 'Order Entry'[Created on] )
var MinCreatedOn =
    MIN( 
        ALLSELECTED( 'Order Entry' ), 
        'Order Entry'[Created on] 
    )
var RollingWindow = 28
var SelectedOrdersPlusWhatsBefore =
    union(
        FILTER(
            ALLSELECTED( 'Order Entry' ),
            CurrentCreatedOn - RollingWindow <= 'Order Entry'[Created on]
            && 
            'Order Entry'[Created on] <= CurrentCreatedOn                
        ),
        FILTER(
            EXCEPT(
                ALL( 'Order Entry' ),
                ALLSELECTED( 'Order Entry' )
            ),
            'Order Entry'[Created on] < MinCreatedOn
            &&
            CurrentCreatedOn - RollingWindow <= 'Order Entry'[Created on]
            && 
            'Order Entry'[Created on] <= CurrentCreatedOn
        )
    )
var Result =
    CALCULATE (
        [Sales],
        SelectedOrdersPlusWhatsBefore
    )
return
    Result
  • MagikJukas's avatar
    MagikJukas
    Icon for Resolver III rankResolver III
    4 years ago

    Thanks for your suggestion, but it did not work.

     

    I was looking for a more immediate and simple solution, which does not seem to be available.

    Eventually, I decided to keep 2018 series but to keep it hidden in white color.