cancel
Showing results for
Did you mean:
Frequent Visitor

## how to get last 2nd Quarter 3MMA(1 april to 30 June from selected date

Hi All,

I have Date Dimension table where in we have quarter/Year/Date details in it. Also am keeping this as a Report level filter wrt to Month. I wanted to know the sum/3MMA of last year's 2nd quarters data when we select a date from date slicer.

3MMA EndOfFY Previous = CALCULATE(
[Net Promoter Score],
DATESBETWEEN(
'Date Dimension'[Date]
), FILTER(ALL('Date Dimension'),'Date Dimension'[Quarter]= 2

Thank You:

Nishanth

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: how to get last 2nd Quarter 3MMA(1 april to 30 June from selected date

Create another calendar date table without connecting to your tables

`calendar1 = ADDCOLUMNS(CALENDARAUTO(),"year",YEAR([Date]))`

```Measure =
CALCULATE(MIN(calendar1[year]),ALLSELECTED(calendar1))

Measure 2 =
IF(MAX(Sheet4[year])=[Measure]&&
MAX(Sheet4[month])>=4&&MAX(Sheet4[month])<=6,1,0)

Measure 3 =
CALCULATE(SUM(Sheet4[value]),FILTER(ALL(Sheet4),[Measure 2]=1))```

Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 REPLIES 4
Community Support Team

## Re: how to get last 2nd Quarter 3MMA(1 april to 30 June from selected date

Do you want to create this measure based on any selected date from the slicer or regardless of any selected slicer?

If it is based on any selected date from the slicer, what is selected so that this measure would show last year's 2nd quarters data?

Best Regards

Maggie

Highlighted
Frequent Visitor

## Re: how to get last 2nd Quarter 3MMA(1 april to 30 June from selected date

Yes, I have a report level filter of Normal year date. My requirement is as below

1. If the selected date(Slicer) is from July 2018 to June 2019 then 3MMA june should be of April 18 to Jun 18 and if it is selected between July 2019 to June 2020 it should display April 19 to June 19 data as a 3 MMA.
More: In Date Dimension table is having normal quarter  number/Start Date and End date also, Our client FY is from July to June.
So FY Quarter data is also there in Date dimension table. So to conclude Quarter number for Normal calender is 2 but it is 4 for our Client FY Calander.

Nishanth

Community Support Team

## Re: how to get last 2nd Quarter 3MMA(1 april to 30 June from selected date

Create another calendar date table without connecting to your tables

`calendar1 = ADDCOLUMNS(CALENDARAUTO(),"year",YEAR([Date]))`

```Measure =
CALCULATE(MIN(calendar1[year]),ALLSELECTED(calendar1))

Measure 2 =
IF(MAX(Sheet4[year])=[Measure]&&
MAX(Sheet4[month])>=4&&MAX(Sheet4[month])<=6,1,0)

Measure 3 =
CALCULATE(SUM(Sheet4[value]),FILTER(ALL(Sheet4),[Measure 2]=1))```

Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team

## Re: how to get last 2nd Quarter 3MMA(1 april to 30 June from selected date

Is this problem sloved?

If not, please let me know.

Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.