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

Kaiden — Wed, 03 Jul 2024 12:46:47 GMT

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))

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!

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

Anonymous — Thu, 04 Jul 2024 07:34:02 GMT

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.

topic Re: DAX to fill in a quarter with an estimate if distinct months within a quarter <> 3 in DAX Commands and Tips

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

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