cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Abhaykumar
Microsoft
Microsoft

Sum over specific range

I have a table Jobs:

 

2017-09-14 19_09_17-Untitled - Query Editor.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

And another table DaySeries (for X-Axis):

 

2017-09-14 19_12_47-Untitled - Query Editor.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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!

1 ACCEPTED SOLUTION
v-ljerr-msft
Microsoft
Microsoft

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. Smiley Happy

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

r2.PNG

 

Regards

View solution in original post

4 REPLIES 4
v-ljerr-msft
Microsoft
Microsoft

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. Smiley Happy

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

r2.PNG

 

Regards

View solution in original post

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

Ross73312
Community Champion
Community Champion

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.

(I no longer have access to this account)

@Ross73312, 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.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.