Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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...
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    7 years ago

    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
nn1233__'s avatar
nn1233__
New Member
4 years ago

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?

Deep21's avatar
Deep21
Frequent Visitor
4 years ago
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