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
gb1997
Regular Visitor

7-day rolling sum issue with slicer

Hi everyone,

 

I am having some issueswith my DAX code.

 

I have an Excel table named 'MyTable' that I use as a dataset. In this table there are several columns, including one for dates (from 2019 to 2021) and one for the number of orders I have per day.

 

I wanted to create a 7-day rolling sum the returns for a given day the number of orders for the current day and the 6 days before.

 

Here is an example:

 

DateOrders7-day rolling sum for orders
Jan 1st55
Jan 2nd27
Jan 3rd613
Jan 4th316
Jan 5th723
Jan 6th427
Jan 7th229
Jan 8th327

 

Here is an example of the kind of results I am supposed to get. Here, for Jan 8th, I should get the sum of Jan 8th + the 6 days before.

 

My DAX formula is the following:

 

Orders 7DR Sum =
CALCULATE(
    SUMX('MyTable',MyTable[Orders]),
    DATESINPERIOD(MyTable[Date], LASTDATE(MyTable[Date]), -6, DAY)
)

Technically it works if I show the entire data on my Power BI dashboard. However, I need to use slicers on my dashboard to filter the data. The problem with the slicer is that if I ask it to filter the data for february 2019 for example, the formula will start the sum at the beginning of february. Leading to the first 7 days of the chart being wrong.

How can I modify my DAX formula so that it still considers the dates outside of the slicer?
 
Thank you very much.
3 REPLIES 3
gb1997
Regular Visitor

Any idea? I have been trying many things and nothing works at the moment...

FreemanZ
Super User
Super User

hi @gb1997 

When you say "ask it to filter the data for february 2019", which day do you mean by february 2019?

Hi @FreemanZ ,

 

When I say "filter the data for February 2019" what I mean by that is that I want the slicer to show me the data only for February 2019. All the data that is before Feb 2019 and after is not shown.

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.