Calculating previous quarter Actual data based on the year and month slicer selection

[Deep21]

I implemented this for current month calculation

CQ value =

CALCULATE(SUM('CRAFData'[Actual]),FILTER(ALL('Date_Com'),'Date_Com'[QuarterNo]=ROUNDUP(SELECTEDVALUE('Date_Com'[MonthNo])/3,0))) ,

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('CRAFData'[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.

I was even trying some if conditions , but seems it is also having error in concadination.

PQ value =

VAR v =ROUNDUP(SELECTEDVALUE('Date_Com'[MonthNo])/3,0)-1

Var q1= CALCULATE(SUM('CRAF Data'[Actual]),FILTER(ALL('Date_Com'),CONCATENATEX(Date_Com,'Date_Com'[Year]=(SELECTEDVALUE(Date_Com[Year])-1),Date_Com[QuarterNo]="4")))

var q2= CALCULATE(SUM('CRAF Data'[Actual]),FILTER(ALL('Date_Com'),'Date_Com'[QuarterNo]=ROUNDUP(SELECTEDVALUE('Date_Com'[MonthNo])/3,0)-1 ))

VAR A = IF(v=0 ,q1,q2) return A

Any leads will help .

[v-shex-msft]

Hi @Deep21,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[amitchandak · Accepted solution]

@Deep21 , refer if these can help

Last Qtr =
var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _max = eomonth(_max,-1* if( mod(Month(_max),3) =0,3,Month(_max)))
var _min = eomonth(_min,-3)+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

or

Last Qtr =
var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _max = eomonth(_max,-1* if( mod(Month(_max),3) =0,3,mod(Month(_max),3)))
var _min = eomonth(_min,-3)+1
return
CALCULATE([net] ,Filter('Date','Date'[Date]>= _min && 'Date'[Date] <= _max))
//Or use all
CALCULATE([net] ,Filter(all('Date'[Date]),'Date'[Date]>= _min && 'Date'[Date] <= _max))

QTD =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = eomonth(_max,-1* if( mod(Month(_max),3) =0,3,mod(Month(_max),3)))+1
return
CALCULATE([net] ,Filter('Date','Date'[Date]>= _min && 'Date'[Date] <= _max))

Last year QTD =
var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _max = date(Year(_max1) -1, month(_max1) , Day(_max1))
var _min = eomonth(_max,-1* if( mod(Month(_max),3) =0,3,mod(Month(_max),3)))+1
return
CALCULATE([net] ,Filter('Date','Date'[Date]>= _min && 'Date'[Date] <= _max))

Last year full qtr =
var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _max2 = date(Year(_max1) -1, month(_max1) , Day(_max1))
var _min = eomonth(_max2,-1* if( mod(Month(_max2),3) =0,3,mod(Month(_max2),3)))+1
var _max = eomonth(_min,2)
return
CALCULATE([net] ,Filter('Date','Date'[Date]>= _min && 'Date'[Date] <= _max))

or

Qtr =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = eomonth(_max,-1* if( mod(Month(_max),3) =0,3,mod(Month(_max),3)))+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

MTD =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = eomonth(_max,-1)+1 ,
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

LMTD =
var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _max = date(Year(_max1), month(_max1)-1, day(_max))
var _min = eomonth(_max1,-2)+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

Last Month =
var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _max = eomonth(_max1,-1)
var _min = eomonth(_max1,-2)+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))