Forum Discussion

philongxpct's avatar
philongxpct
Frequent Visitor
9 years ago
Solved

Custom rolling calculation

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 mont...
Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    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's avatar
philongxpct
Frequent Visitor
9 years ago
Thanks. Great appriciate Angle ^^. I'll follow then let u know lol.
v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
9 years ago

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