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 ...
daXtreme's avatar
daXtreme
Icon for Solution Sage rankSolution Sage
4 years ago

I don't understand why you use ALLSELECTED in your formula. Why is it there? Can you please explain? Thanks.

 

I think this might be what you're looking for:

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

[Rolling28 Sales] =
var CreatedOn = MAX( 'Order Entry'[Created on] )
var RollingWindow = 28
var Result =
    CALCULATE (
        [Sales],
        FILTER(
            ALL( 'Order Entry'[Created on] ),
            CreatedOn - RollingWindow <= 'Order Entry'[Created on]
            && 
            'Order Entry'[Created on] <= CreatedOn
        )
    )
return
    Result

 

  • 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.