cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nmaheshwarappa Frequent Visitor
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]
,DATEADD(STARTOFQUARTER('Date Dimension'[Date]),-12,MONTH)
,DATEADD(ENDOFQUARTER('Date Dimension'[Date],-12,MONTH)
), FILTER(ALL('Date Dimension'),'Date Dimension'[Quarter]= 2

 

I am using this but getting blanck values. Please help.

 

Thank You:

Nishanth

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

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

Hi @nmaheshwarappa 

Create another calendar date table without connecting to your tables

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

create measures in your table

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))

6.png

 

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
Community Support Team

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

Hi @nmaheshwarappa 

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
nmaheshwarappa Frequent Visitor
Frequent Visitor

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

Hi @v-juanli-msft 

 

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. 
 
Please help me on this.
 
Thanks in Advance:
Nishanth
 
 
Community Support Team
Community Support Team

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

Hi @nmaheshwarappa 

Create another calendar date table without connecting to your tables

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

create measures in your table

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))

6.png

 

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
Community Support Team

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

Hi @nmaheshwarappa 

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.