Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |