Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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))
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.
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.
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))
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.
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
Yes, I have a report level filter of Normal year date. My requirement is as below
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |