topic Re: Custom rolling calculation in Desktop

Custom rolling calculation

philongxpct — Mon, 20 Feb 2017 07:48:25 GMT

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

Re: Custom rolling calculation

v-huizhn-msft — Tue, 21 Feb 2017 04:56:22 GMT

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

Re: Custom rolling calculation

philongxpct — Fri, 24 Feb 2017 03:36:01 GMT

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

Re: Custom rolling calculation

v-huizhn-msft — Mon, 27 Feb 2017 09:06:12 GMT

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