Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
andy_pat
Helper I
Helper I

Rolling Calculation with Filter

I have a rolling sum calculation as my measure. The measure works fine, but the additonal requirement is to hide the last 7 days of calculated data.  The data older than 7 days should show up even if the slicer on Date column is moved around.

See pbix attached here

 

Moving sum = CALCULATE(SUM(Data[Value]),
                      DATESINPERIOD(Data[Date],LASTDATE(Data[Date]),-7,DAY)
)

 

 The Grand Total also needs to reflect the correct number, which at the moment is incorrectly showing as 7

Any guidance would be really helpful.

 

Adding more detail: 

 The dates from 7-26 to 08-01 should return blank value for the measure, when the latest date is 08-01.  The window of blank data should change from 7-27 to 08-02 the latest date changes to 08-02  In other words, trying to blank out the latest 7 days of data.

andy_pat_0-1600997490850.png

Part 2: 
The "Total" still shows 7. Need to fix that to reflect the correct value.

 

 

Thanks

Andy

1 ACCEPTED SOLUTION

@andy_pat , Try measure like these, see if this can help

measure =
var _max = maxx(all(Date), Date[Date])
return
calculate(if(max(Date[Date]) >=_max-7, blank(), [measure])) // you can use 0 in place blank

 

measure =
var _max = maxx(all(Date), Date[Date])
return
Sumx(values(Date[Date]), if(max(Date[Date]) >=_max-7, blank(), [measure])) // you can use 0 in place blank

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@andy_pat , not very clear .The information you have provided is not making the problem clear to me. Can you please explain with an example.

Something like this

Moving sum before 7 days= CALCULATE(SUM(Data[Value]),
                      all(Date)) - CALCULATE(SUM(Data[Value]),
                      DATESINPERIOD(Data[Date],mx(Data[Date]),-7,DAY))

 

or

7 Day behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-7,Day))



Appreciate your Kudos.


Thanks. I added a scenario in my post.  The goal is to calculate the moving sum for all dates except the last 7 available dates, as the date range advances each day.

@andy_pat , Try measure like these, see if this can help

measure =
var _max = maxx(all(Date), Date[Date])
return
calculate(if(max(Date[Date]) >=_max-7, blank(), [measure])) // you can use 0 in place blank

 

measure =
var _max = maxx(all(Date), Date[Date])
return
Sumx(values(Date[Date]), if(max(Date[Date]) >=_max-7, blank(), [measure])) // you can use 0 in place blank

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors