cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted Microsoft

## Re: Custom rolling calculation

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 3
Highlighted Microsoft

## Re: Custom rolling calculation

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

Highlighted
Frequent Visitor

## Re: Custom rolling calculation

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

## Re: Custom rolling calculation

Hi @philongxpct,

Best Regards,
Angelia

Announcements #### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge! #### Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events. #### Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp. Top Solution Authors
Top Kudoed Authors
Users online (1,594)