DATESINPERIOD calculation question

[anandav]

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.

[v-juanli-msft]

Hi 

Thank you for sharing !

Best Regards

Maggie

[anandav · Accepted solution]

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.