Moving average of last 6 months excluding current month for just last 12 months

[JJha75]

Hi all,
I need your help.
I am stuck in a DAX problem, my scenairio is
I have a date table and I have a project table with some score. I want to calculate moving average of 6 months of that score. The problem is in project table period first doesn't have data from past, so from start period upto month 5 there will be average of only available data period. i.e. in first period average will be just that month, in 2nd period average will be just for 2 months that is period one and two, that will go up to period 5. So there will be only complete six month average in period 6. Currently, my calculation is doing average even in first peried it is dividing first period score by 6, which is wrong and the average score get two low. Also I want only last twelve month display in the chart, currently I can't control it. Required correct data sample is below.

FISCAL period from Project table	Score	6 months moving average
1/01/2024	10	10
1/02/2024	12	11
1/03/2024	8	10
1/04/2024	12	10.5
1/05/2024	14	11.2
1/06/2024	9	10.83
1/07/2024	12	11.17
1/08/2024	11	11
1/09/2024	8	11
1/10/2024	13	11.17
1/11/2024	9	10.33
1/12/2024	10	10.5
1/01/2025	11	10.33

I have deadline 
Please help in this regard. 
Regards,
Jag

[DataNinja777 · Accepted solution]

Hi @JJha75 ,

There are many ways to produce your required output, and one of them is by creating a calculated column like below: 

Moving average (6 months) = 
VAR EndDate = 'Table'[FISCAL period from Project table]
VAR StartDate = EOMONTH(EndDate, -6) + 1

VAR Cumulative_Score = 
    CALCULATE(
        SUM('Table'[Score]),
        'Calendar'[Date] <= EndDate &&
        'Calendar'[Date] >= StartDate
    )

VAR Number_of_Months = 
    CALCULATE(
        COUNTROWS('Table'),
        'Calendar'[Date] <= EndDate &&
        'Calendar'[Date] >= StartDate
    )

RETURN
IF(
    Number_of_Months > 0,
    divide(Cumulative_Score , Number_of_Months),
    BLANK()
)

The resultant output matches with your required output:  

Best regards,

[mickey64]

For your reference.

MOVINGAVERAGE function (DAX) - DAX | Microsoft Learn

Using visual calculations in Power BI Desktop - Power BI | Microsoft Learn

Calculation = MOVINGAVERAGE([Sum of Score],6,ROWS)

[JJha75]

Thanks Mickey.
This is exactly the calculation I am looking for but not in the table, I want the same calculation in measure to use in chart. If you could find me the logic how that moving average works then I can use it in the Line & clustered column chart.