Forum Discussion
Skilled SharerDATESINPERIOD calculation question
Hi All,
Below is my data:
| Compnay | Date | Amount |
| A | 01/01/2018 | 10 |
| B | 01/01/2018 | 20 |
| B | 02/01/2018 | 20 |
| A | 01/02/2018 | 30 |
| A | 01/02/2018 | 10 |
| B | 02/02/2018 | 40 |
| B | 03/02/2018 | 50 |
| A | 01/03/2018 | 50 |
| B | 01/03/2018 | 60 |
| A | 01/04/2018 | 70 |
| A | 01/04/2018 | 50 |
| A | 02/04/2018 | 40 |
| A | 03/04/2018 | 70 |
| B | 04/04/2018 | 80 |
| A | 01/05/2018 | 90 |
| B | 01/05/2018 | 100 |
| A | 01/06/2018 | 110 |
| B | 01/06/2018 | 120 |
| B | 02/06/2018 | 130 |
| B | 03/06/2018 | 140 |
| B | 04/06/2018 | 140 |
| B | 05/06/2018 | 150 |
| B | 06/06/2018 | 160 |
I am using the following DAX formula to calculate moving / rolling average for the past 2 months.
Past X Months Average =
CALCULATE(
AVERAGE(PrevMonth[Amount]),
DATESINPERIOD(PrevMonth[Date],
LASTDATE(PrevMonth[Date]),
-2,
MONTH
)
)
When I use the date with no heirarchy I get the correct result for the last day of the month.
But when I try to use the date hierarchy and display the year and month with the average, it is not giving the correct results.
Any suggestion why this is happening?
I do not have a calendar table.
Any help will be much appreciated.
I figured out that I need to have a calendar table to solve the above problem.
If anyone is interested in the solution please see this blog.
2 Replies
- anandav
I figured out that I need to have a calendar table to solve the above problem.
If anyone is interested in the solution please see this blog.
v-juanli-msftCommunity Support
Hi
Thank you for sharing !
Best Regards
Maggie