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 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?

5 Replies

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

    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