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.
