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 month of this quarter.

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

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

     

3 Replies

  • 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