Help calculating a 7 day running total column

[kent_iglehart]

I have a table in PowerBI that has the column "Date" and "Sales". I want to create a measure and display it in a table that computes a rolling 7 day total of the "Sales" column. To be clear, I want to see this over time, I do not want it for a single day, I want to create a table exactly like I am showing below, thanks!

Date	Sales	Running 7 Day Total
1/1/2020	72
1/2/2020	5
1/3/2020	34
1/4/2020	45
1/5/2020	81
1/6/2020	60
1/7/2020	63	360
1/8/2020	6	294
1/9/2020	53	342
1/10/2020	30	338
1/11/2020	32	325
1/12/2020	35	279
1/13/2020	82	301
1/14/2020	55	293
1/15/2020	99	386
1/16/2020	97	430
1/17/2020	100	500
1/18/2020	54	522

[Ashish_Mathur · Accepted solution]

Hi,

Assuming there is a sale recorded for everyday, try this:

1. Create a Calendar Table
2. Build a relationship from the Date column of the Data Table to the Date column of the Calendar Table
3. To your visual, drag the Date field from the Calendar Table
4. Write these measures

Total sales = SUM(Data[Sales])

7 days rolling sales = CALCULATE([Totalsales],DATESBETWEEN(Calendar[Date],MIN(Calendar[Date])-6,MIN(Calendar[Date])))

Hope this helps.

[kent_iglehart]

@Ashish_Mathur  thank you so much that worked amazing and did exactly what I wanted. I can't tell you much I appreciate your help : )!

[Ashish_Mathur]

You are welcome.

[amitchandak]

In case you use date calendar

Rolling 7 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),-7,MONTH))  
Rolling 7 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Date[Date]),-7,MONTH))  

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[Greg_Deckler]

I can't think of a time intelligence measure for that. See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...