Custom rolling calculation

[philongxpct]

I'm just a new guys on DAX. I have a function of sales: P= ((S0+S3)/2+S1+S2)/3

S0: Last month of previous quarter.

S1: First month of this quarter.
S2: second month of this quarter.

S3: last month of this quarter.

DATA run over years to years. Can anyone can help me how to calculate that, pls @@

[v-huizhn-msft · Accepted solution]

Hi @philongxpct,

First, you should create calculated columns month, quarter, the first month of this quarter, the second month of this quarter, the end month of this quarter. 

MonthOfYear = MONTH(Table1[Date])

Quarter = ROUNDUP(MONTH([Date])/3, 0)

Firstt = MONTH(STARTOFQUARTER(Table1[Date]))

End = MONTH(ENDOFQUARTER(Table1[Date]))

Second month = (CALCULATE(MAX(Table1[MonthOfYear]),ALLEXCEPT(Table1,Table1[Quarter])) +CALCULATE(MIN(Table1[MonthOfYear]),ALLEXCEPT(Table1,Table1[Quarter])))/2

Then create measures using the following formulas.

S1 = CALCULATE(SUM(Table1[Sale]),FILTER(Table1,Table1[MonthOfYear]=Table1[Firstt]))

S2 = CALCULATE(SUM(Table1[Sale]),FILTER(Table1,Table1[MonthOfYear]=Table1[Second month]))

S3 = CALCULATE(SUM(Table1[Sale]),FILTER(Table1,Table1[MonthOfYear]=Table1[End]))

S0 = CALCULATE(Table1[Last],PARALLELPERIOD(Table1[Date],-1,QUARTER))

If you have other issues, don't hesitate to let me know.

Best Regards,
Angelia

[philongxpct]

Thanks. Great appriciate Angle ^^. I'll follow then let u know lol.

[v-huizhn-msft]

Hi @philongxpct,

If you have resolved your issue, please mark the corresponding reply as answer, which will help more people. If you still have issue, please feel free to ask.

Best Regards,
Angelia