cancel
Showing results for 
Search instead for 
Did you mean: 
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
Community Champion
Community Champion

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
Exciting changes

Power BI Community Changes

Check out the changes to the Power BI Community announced at Build.

May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Kudo Data Story carousel

Data Stories Gallery

Visit our Data Stories Gallery and give kudos to your favorite Data Stories.

Top Solution Authors