Sum over specific range

[Abhaykumar]

I have a table Jobs:

And another table DaySeries (for X-Axis):

There is many to one relationship between Jobs[Day] -> DaySeries[Days].

I want  to display total number jobs in previous 10 days (including that day).

So: for each value X from DaySeries[Days] , SUM(Jobs[Jobs])   if Jobs[Day] is in previous 10 days of X.

I have these integers as days value and not actual dates.

I tried using calculate and filter but unable to form the condition for filter.

Thanks for your help!

[v-ljerr-msft · Accepted solution]

Hi @Abhaykumar,

Based on my test, you should be able to use the formula below to create a measure to get the total number jobs in previous 10 days (including that day) in your scenario. 

Measure =
VAR currentDay =
    MAX ( DaySeries[Days] )
RETURN
    CALCULATE (
        SUM ( Jobs[Jobs] ),
        FILTER (
            ALL ( DaySeries ),
            DaySeries[Days]
                > currentDay - 10
                && DaySeries[Days] <= currentDay
        )
    )

Regards

[Abhaykumar]

Thanks @v-ljerr-msft, that worked for me!.

[Anonymous]

Before offering a solution, i'd first query whether you are writing yourself into a corner.  By only specifying DAY, how will you make sure you aren't lining up data from different months?  Or is it a case of wanting to do that?

Also want to know how you will had the math for days 1 to 9?  If we are looking at past 10 days, do you loop back around to 31?  Will you get bad information when the prior month was 28 or 30 days?

If i could offer some advice, i'd use a DATE column, then for your Axis, you can display using the 'DAY' portion of the date.  This can be done either by Formatting the date into DD-MMMM format, or creating a calcualted column that gets your existing DAY.  Now you can easily get the last 10 days using the visual, selecting sum and placing in a Relative Date Slicer to only show last 10 days.

[Abhaykumar]

@Anonymous, I am always concerned with last 50-100 days and do not need to know specific date/month. My users are aware of the situation in which first X days would not have all data if I look back X days. Also 10 is just a placeholder, it can be some varying number depending on requirement. 

What we want is to compare different sequence of events as they progress over time, not necesssarily at the same time. Hence we have data normalized to day 1 and then onwards. Let me know if you need any other info.