DAX to fill in a quarter with an estimate if distinct months within a quarter <> 3

[Kaiden]

Hi all,

I have a Matrix that compares Quarter over Quarter numbers, as well as yearly quarters (Q2 2023 vs Q2 2024).

What I am needing is a Measure that fills in empty months within a quarter with the average value of the last 3 completed months.

Example: I have April and May data for Q2, I need something that would fill in June with an "Estimate" that is the average of the last 3 months, in this case March, April, May. 

I would need this to be done for all out months.

This is my current DAX for calculating the Estimate which seems to be working, I just need help with the part of applying this in the right scenarios:

3 Month Average "Estimate" = CALCULATE(

    SUM(Table1[Cost]),

     DATEADD(Table1[Date.[Date],-3,MONTH))

     /3

What I imagine the setup would be for what I am needing is:

IF (DISTINCT Months IN Qtr = 0) THEN ~DAX for Average from last 3 months~
Applied to PARALLELPERIODS +1 & +2 & +3

IF (DISTINCT Months IN Qtr = 1) THEN ~DAX for Average from last 3 months~
Applied to PARALLELPERIODS +1 & +2

IF (DISTINCT Months IN Qtr = 2) THEN ~DAX for Average from last 3 months~
Applied to PARALLELPERIODS +1

IF (DISTINCT Months IN Qtr = 3) THEN Do Nothing

Thanks!

[Anonymous · Accepted solution]

Hi,@Kaiden  I am glad to help you.
You can refer to my test below

M_result = 
VAR _DATE=CALCULATE(MAX(Sheet2[Date]),FILTER(ALL(Sheet2),'Sheet2'[QuarterNum]=MAX('Sheet2'[QuarterNum])&&'Sheet2'[YearNum]=MAX('Sheet2'[YearNum])))
VAR _DATE_Two=EOMONTH(_DATE,-3)+1
 RETURN
 
 SWITCH(TRUE(),
[M_eachMonNum_Aquarter1] = 1,
SUMX(FILTER(ALL(Sheet2),Sheet2[Date]>=_DATE_Two&&Sheet2[Date]<=_DATE),Sheet2[Cost])/3,
[M_eachMonNum_Aquarter1] = 2,
SUMX(FILTER(ALL(Sheet2),Sheet2[Date]>=_DATE_Two&&Sheet2[Date]<=_DATE),Sheet2[Cost])/3,
[M_eachMonNum_Aquarter1] = 3,
SUMX(FILTER(ALL(Sheet2),Sheet2[Date]>=_DATE_Two&&Sheet2[Date]<=_DATE),Sheet2[Cost])/3
 )

calculate column

YearNum = YEAR('Sheet2'[Date])


QuarterNum = QUARTER('Sheet2'[Date])


MonthNum = MONTH('Sheet2'[Date])

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.