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

Calculating quarter data based on the month slicer selection

Hi All,
I have a scenerio where i need to calculate the total sum of a measure for the current quarter but the problem is there is an report level filter which is MONTH
So the requirement is when a user selects any month in the slicer the total sum would be the sum for the quarter to which the month falls to

Suppose the user has selected MAY as the month slicer,the measure would show the total sum for the particular quarter which is nothing but (APRIL,MAY and JUNE)

I tried using the below query but the data is returning each month data rather the quarter data.

IF(ISFILTERED('C&SB'[Month]),
CALCULATE(SUM('C&SB'[Actual]),DATESBETWEEN('C&SB'[Date],DATEVALUE(STARTOFQUARTER(ALLSELECTED('C&SB'[Date]))),DATEVALUE(ENDOFQUARTER(ALLSELECTED('C&SB'[Date]))))))

Help would be really appreciated

Regards
Prabin Nepak

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi Prabin,

 

Filter the scope with calculating the quarter of the selected month from slicer like below:

 

CALCULATE(SUM('C&SB'[Actual]),FILTER(ALL('Date Dimension'),'Date Dimension'[Quarter]=ROUNDUP(SELECTEDVALUE('Date Dimension'[MonthN])/3,0)))
 
Quarter & MonthN are numbers respectively.
 
Hope it helps.
 
Cheers,
Amit

View solution in original post

6 REPLIES 6
Deep21
Frequent Visitor

Hi , both 

I implemented this for current month calculation ,and it worked fine .However same i would like to implement for previous month , can anyone let me know how to use DAX formula for the same .

Currently i am using ,

PQ value = CALCULATE(SUM('CRAF Data'[Actual]),FILTER(ALL('Date_Com'),'Date_Com'[QuarterNo]=ROUNDUP(SELECTEDVALUE('Date_Com'[MonthNo])/3,0)-1))
This is not working correctly for first three months(quarter 1) selected for the current year.
Anonymous
Not applicable

Hi Prabin,

 

Filter the scope with calculating the quarter of the selected month from slicer like below:

 

CALCULATE(SUM('C&SB'[Actual]),FILTER(ALL('Date Dimension'),'Date Dimension'[Quarter]=ROUNDUP(SELECTEDVALUE('Date Dimension'[MonthN])/3,0)))
 
Quarter & MonthN are numbers respectively.
 
Hope it helps.
 
Cheers,
Amit

Hi Anonymous,

 

Thank you!

I would like to ask if you know how I can add a filter for the year (based on a slicer) on this expression?

Hi @nn1233__ ,
I used the below dax for month and year slicer for current quarter calculations , you can try this :
 
Current Qtr Actual =
VAR selected_month = SELECTEDVALUE('Date_Com'[MonthNo])
var selected_year = SELECTEDVALUE('Date_Com'[Year])
var y =
IF(selected_month in {"01","02","03"} ,(CALCULATE(SUM('CRAF Data'[Actual]),FILTER(ALL('Date_Com'),'Date_Com'[QuarterNo]=ROUNDUP(SELECTEDVALUE('Date_Com'[MonthNo])/3,0) && Date_Com[Year]=selected_year))),
IF(selected_month in {"04","05","06"} ,(CALCULATE(SUM('CRAF Data'[Actual]),FILTER(ALL('Date_Com'),'Date_Com'[QuarterNo]=ROUNDUP(SELECTEDVALUE('Date_Com'[MonthNo])/3,0) && Date_Com[Year]=selected_year))),
IF(selected_month in {"07","08","09"} ,(CALCULATE(SUM('CRAF Data'[Actual]),FILTER(ALL('Date_Com'),'Date_Com'[QuarterNo]=ROUNDUP(SELECTEDVALUE('Date_Com'[MonthNo])/3,0)&& Date_Com[Year]=selected_year))),
IF(selected_month in{"10","11","12"} ,(CALCULATE(SUM('CRAF Data'[Actual]),FILTER(ALL('Date_Com'),'Date_Com'[QuarterNo]=ROUNDUP(SELECTEDVALUE('Date_Com'[MonthNo])/3,0)&& Date_Com[Year]=selected_year))) ))))
return y

Hi , both 

I implemented this for current month calculation ,and it worked fine .However same i would like to implement for previous month , can anyone let me know how to use DAX formula for the same .

Currently i am using ,

PQ value = CALCULATE(SUM('CRAF Data'[Actual]),FILTER(ALL('Date_Com'),'Date_Com'[QuarterNo]=ROUNDUP(SELECTEDVALUE('Date_Com'[MonthNo])/3,0)-1))
This is not working correctly for first three months(quarter 1) selected for the current year.
Anonymous
Not applicable

Hi Amit,

It was really helpful 

Thanks
Prabin Nepak

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.