cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Running sum last 6 months measure

Currently trying to make a running sum of Number1 & Number2 for last 6 months. Something weird is happening at the end, and the logic does not follow the intended calculations.

I used following formula:

 

 

 

Number1_L6M_sum = 
CALCULATE (
    SUM ( Table1[Number1] ),
    filter(all(Table1[Date]), Table1[Date] < min(Table1[Date]) && DATEADD(Table1[Date], 6, MONTH) >= min(table1[Date]))
)

 

 

But as you can numbers start to not add up as they approach current date. (Green on left should match green on right and red ditto)

Why is this happening - And what is a potential solution?

 
 

 

 

Udklip.JPG

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Running sum last 6 months measure

Hi @ChristianRHouen ,

 

I have created a sample for your reference, Please create a measure as below to work on it.

 

Measure = 
VAR pre6 =
    EDATE ( MAX ( 'Table 1'[date] ), -6 )
VAR cure =
    MAX ( 'Table 1'[date] )
RETURN
    CALCULATE (
        SUM ( 'Table 1'[Number1] ),
        FILTER ( ALL ( 'Table 1' ), 'Table 1'[date] >= pre6 && 'Table 1'[date] <= cure )
    )

 

Capture.PNG

 

For more details, please check the pbix as attached.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

4 REPLIES 4
Highlighted
Community Support
Community Support

Re: Running sum last 6 months measure

Hi @ChristianRHouen ,

 

I have created a sample for your reference, Please create a measure as below to work on it.

 

Measure = 
VAR pre6 =
    EDATE ( MAX ( 'Table 1'[date] ), -6 )
VAR cure =
    MAX ( 'Table 1'[date] )
RETURN
    CALCULATE (
        SUM ( 'Table 1'[Number1] ),
        FILTER ( ALL ( 'Table 1' ), 'Table 1'[date] >= pre6 && 'Table 1'[date] <= cure )
    )

 

Capture.PNG

 

For more details, please check the pbix as attached.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

Highlighted
Helper II
Helper II

Re: Running sum last 6 months measure

Thanks, Frank!

Unfortunately, this solution doesn't seem to scale well, as the underlying data model actually has many rows for each month, and Number1 is actually a Number1 = sum(underlying_number1).

This means that for the sum, I can easily view it in different date buckets, be they year, month, week. 

Your solution is definitely more correct than mine, but even if I change the date in your file to years it becomes non-sensical.

Udklip.JPG
Do you have a different solution maybe?

Highlighted
Helper II
Helper II

Re: Running sum last 6 months measure

I stand corrected - it's actually quite flexible. The only reason I had issues was that the code should be 

 

 

'Table 1'[date] <= cure )

Instead of

'Table 1'[date] < cure )

 

 

 in order to include the last day of the month.

Would you correct your answer, so I can mark it as correct? 🙂  

Highlighted
Community Support
Community Support

Re: Running sum last 6 months measure

Hi @ChristianRHouen ,

 

I have updated my previous post:)

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors