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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors