cancel
Showing results 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

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:

 Date Orders 7-day rolling sum for orders Jan 1st 5 5 Jan 2nd 2 7 Jan 3rd 6 13 Jan 4th 3 16 Jan 5th 7 23 Jan 6th 4 27 Jan 7th 2 29 Jan 8th 3 27

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

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

Super User

hi @gb1997

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

Regular Visitor

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors