Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Dynamic Date- Past X months data

Hi 

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:

MonthSales
1 Aug 2019100
1 Sep 2019120
1 Oct 2019110

 

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

MonthSales
1 Aug 2019100
1 Sep 2019120
1 Oct 2019130
1 Nov 2019107

 

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

CompanyPrevious month ( ie Oct)Previous 3 months avg sales (Aug- Oct)
A130116.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!

 

 

Date
DAX
Dynamic Date Period
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi

     

    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

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi

     

    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