Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
v-juanli-msft
Community Support
Community Support

Hi @Anonymous 

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.

View solution in original post

4 REPLIES 4
v-juanli-msft
Community Support
Community Support

Hi @Anonymous 

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.

v-juanli-msft
Community Support
Community Support

Hi @Anonymous 

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.

v-juanli-msft
Community Support
Community Support

Hi @Anonymous 

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

Anonymous
Not applicable

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
 
 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.