cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Anonymous
Not applicable

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:

 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!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

Anonymous
Not applicable

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

Announcements

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors