Date column - last thirty days and total sales

[CABIRDUK]

I have a table with Date & Sales figures.

I want a rolling measure to be able to extract the last 30 days a total number of sales? For example last 30 days a total number of sales was 200.

I have spent a full day researching forums, blogs, videos and haven't quite been able to find a suitable solution. I require automated solution i.e. user not asked to enter a date. New date and sales figures are added each month. 

Thank you for reading this message and any assistance you may offer. 

[MarcoRotta · Accepted solution]

Hi CABIRDUK.

It seems that you need a rolling sum for the last X periods of time, right? Let me show you one way to do that.

As you haven't shared any data set, let me use the following table (loaded as "Test") to demonstrate:

Now I've added a measure with the following DAX formula to calculate the rolling sum of the last X days. Hopefully, you'll be able to understand the formula as you read it:

Rolling sum = CALCULATE(sum(Test[Sales]),
          DATESINPERIOD(Test[Date],
                        LASTDATE(Test[Date]),-5, DAY
                       )
         )

I'm calculating the rolling sum of the last 5 days here (my dataset is not that large), but you can change to 30 or anything you like. 

Then, just added the measure to a visual, and here we go.

Hope it helps.

Regards.

[CABIRDUK]

Absolutley brilliant.

Thank you. 

Yesterday was very, very frustrating. 

Have a fabulus day.