cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Microsoft Abhaykumar
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

Accepted Solutions
Microsoft v-ljerr-msft
Microsoft

Re: Sum over specific range

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
Ross73312 Super Contributor
Super Contributor

Re: Sum over specific range

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.


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


Microsoft Abhaykumar
Microsoft

Re: Sum over specific range

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

Microsoft v-ljerr-msft
Microsoft

Re: Sum over specific range

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

Highlighted
Microsoft Abhaykumar
Microsoft

Re: Sum over specific range

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

Helpful resources

Announcements
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors