Dynamic Date- Past X months data

Anonymous — Wed, 13 Nov 2019 16:41:50 GMT

I am working on sales data by month. Every month I get fresh monthly sales data, so I keep appending my query.

For example in November I have data:
Data of company A:

Month	Sales
1 Aug 2019	100
1 Sep 2019	120
1 Oct 2019	110

Then next month, in December by Query will become,
Data of company A

Month	Sales
1 Aug 2019	100
1 Sep 2019	120
1 Oct 2019	130
1 Nov 2019	107

Now at any point of time, Regardless of the current month, I always want to produce a table with sales for 1) previous month and 3) average sales of the past 3 months.

So my latest table will look like this

Company	Previous month ( ie Oct)	Previous 3 months avg sales (Aug- Oct)
A	130	116.66 (ie avg of 130+ 120+110)

How can I create measure for Past 3 month sales, using dynamic dates?

For the first column, I successfully used:
Recent month sales

= CALCULATE(SUM('Sheet1'[Sales ]),PREVIOUSMONTH(DATESMTD('Master'[Month])))

I can not figure out how to extract the sum of sales for Past 3 months (exclusing the most recent month) dynamically (ie this formula should work even next month when the data is refreshed).

Thanks!

Re: Dynamic Date- Past X months data

Anonymous — Thu, 14 Nov 2019 07:08:31 GMT

One way is to add a flag on your calendar that marks the last 3 month dates as true and the rest as false(1,0).

Then

calculate(sum(expression) , Calendar[Past 3 Months Flag] = 1)

Let me know if this helps.

Tomas

topic Re: Dynamic Date- Past X months data in DAX Commands and Tips

Dynamic Date- Past X months data

Re: Dynamic Date- Past X months data