Forum Discussion
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
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
6 Replies
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,AmitHi , 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.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.
