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
Anonymous's avatar
Anonymous
Not applicable
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
  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Amit,

    It was really helpful 

    Thanks
    Prabin Nepak

  • Deep21's avatar
    Deep21
    Frequent Visitor
    4 years ago

    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.
  • 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