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

     

v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
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