Rolling sum excluding filtered values

[MagikJukas]

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 Entry'[Created on]))-28 && 'Order Entry'[Created on]<=MAX('Order Entry'[Created on]))
)

In the chart I exlcluded 2018 sales in the visual filter, but I wish the fomula includes it in the calculation for the first days of 2019:

Any idea?

[Whitewater100]

Hi:

It would be best ohave a date table, connected to your fact table on Dates[Date] to 'Order Entry'[Created On] in one to many relationship. I'll paste Date Table DAX, you can go to modeling>New Table > then paste in. Then use table tools to "mark date field as validate field. Note Date Table is named "Dates"

Then Time Intel Measures work as intended:

RT 28 Day Sales=CALCULATE(SUM('Order Entry'[Sales]),

DATESINPERIOD('Dates[Date],

MAX(Dates[Date]), -28,DAY))  and this will work over years ,etc.

Here is Date Table code:

Dates =

-- Specify a start date and end date

VAR StartDate = Date(2021,1,1)

VAR EndDate = Today() + 243

VAR FiscalMonthEnd = 12

-- Generate a base table of dates

VAR BaseTable = Calendar(StartDate, EndDate)

-- Add the Year for each individual date

VAR Years = ADDCOLUMNS(BaseTable,"Year",YEAR([Date]))

-- Add the calendar month and other month related data for each date

VAR Months = ADDCOLUMNS(

    Years,

    "Month",MONTH([Date]),

    "Year and Month Number",FORMAT([Date],"YYYY-MM"),

    "Year and Month Name",FORMAT([Date],"YYYY-MMM"),

    "Fiscal Year", IF( FiscalMonthEnd = 12, YEAR([Date]), IF( MONTH([DATE]) <= FiscalMonthEnd, YEAR([DATE])-1, YEAR([Date]))),

    "Fiscal Month", IF( FiscalMonthEnd = 12, MONTH([Date]),

        IF( MONTH([Date]) <= FiscalMonthEnd, FiscalMonthEnd + MONTH([Date]), MONTH([Date]) - FiscalMonthEnd))

)

-- Add the Quarter and other quarter related data for each date   

VAR Quarters = ADDCOLUMNS(

    Months,

    "Quarter",ROUNDUP(MONTH([Date])/3,0),

    "Year and Quarter",[Year] & "-Q" & ROUNDUP(MONTH([Date])/3,0))

-- Add the Day and other day related data for each date   

VAR Days = ADDCOLUMNS(

    Quarters,

    "Day",DAY([Date]),

    "Day Name",FORMAT([Date],"DDDD"),

    "Day Of Week",WEEKDAY([Date]),

    "Day Of Year", DATEDIFF (DATE(YEAR([Date]),1,1), [Date], DAY) + 1)

-- Add the Week (assuming each week starts on a Sunday) and other week related data for each date   

VAR Weeks = ADDCOLUMNS(

    Days,

    "Week Of Month (Sunday)",INT((DAY([Date])-1)/7)+1,

    "Week of Year (Sunday)",WEEKNUM([Date],1),

    "Year and Week (Sunday)",[Year] & "-W" & WEEKNUM([Date],1))

-- Add an 'Is Working Day' column which will be true for all days but Saturday and Sunday.

var WorkingDays = ADDCOLUMNS(

    Weeks,

    "Is Working Day", NOT WEEKDAY( [Date] ) IN {1,7})

RETURN WorkingDays

[daXtreme]

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]

Thanks for your comment:

Without Allselected, the chart becomes like this:

Your formula gives the same result.

[daXtreme]

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]

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.