How to get Current Quarter -1 Exp Expression ?

[admin11]

Hi All

I have YTD exp expression working fine :-  

_YTD_EXP = CALCULATE(CALCULATE(GL[AMOUNT_EXP_YTD]*-1,DATESYTD('Date'[Date],"12/31")))

I also have Current Month -1 Exp working fine :-

Current Month - 1 EXP_ =

VAR _Start = EOMONTH ( TODAY (), -2 ) + 1

VAR _End = EOMONTH ( _Start, 0 )

RETURN

CALCULATE ( [AMOUNT_EXP]*-1, DATESBETWEEN ( 'Date'[Date], _Start, _End ) )

May i know how to get Current Quarterly -1 EXP ?

&

May i know how to get Current Quarterly -2 EXP ?

Paul

[amitchandak · Accepted solution]

@admin11 , If you have selected a date on the page

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))

Qtr Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(ENDOFQUARTER('Date'[Date])))

Last QUARTER Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD( ENDOFQUARTER(dateadd('Date'[Date],-1,QUARTER))))
Last QUARTER Sales = CALCULATE(SUM(Sales[Sales Amount]),PREVIOUSQUARTER(('Date'[Date])))

Based on Today

QTD Today=
var _max = maxx(allselected('Date'), 'Date'[Date])
var _month = mod(month(_max),3)
var _min = date(year(_max),month(_max) -1* if(_month=0,3,_month) ,1)
var _day = _max
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Date] >=_min && 'Date'[Date] <= _day) )

LYQTD Today=
var _max = maxx(allselected('Date'), 'Date'[Date])
var _month = mod(month(_max),3)
var _min = date(year(_max)-1,month(_max) -1* if(_month=0,3,_month) ,1)
var _day = date(year(_max)-1, month(_max),day(_max))
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Date] >=_min && 'Date'[Date] <= _day) )

This Qtr =
var _max = maxx(allselected('Date'), 'Date'[Date])
var _month = mod(month(_max),3)
var _min = date(year(_max),month(_max) -1* if(_month=0,3,_month) ,1)
var _day = date(year(_min),month(_min)+3 ,1) -1
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Date] >=_min && 'Date'[Date] <= _day) )

Last Qtr =
var _max = maxx(allselected('Date'), 'Date'[Date])
var _month = mod(month(_max),3)
var _min = date(year(_max)-1,month(_max) -1* if(_month=0,3,_month) ,1)
var _day = date(year(_min),month(_min)+3 ,1) -1
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Date] >=_min && 'Date'[Date] <= _day) )

[admin11]

@amitchandak 

Thank you for the expression for sharing QTR Last EXP below , it working fine .

QTR Last EXP =

var _max = maxx(allselected('Date'), 'Date'[Date])

var _month = mod(month(_max),3)

var _min = date(year(_max)-1,month(_max) -1* if(_month=0,3,_month) ,1)

var _day = date(year(_min),month(_min)+3 ,1) -1

return

CALCULATE ( [AMOUNT_EXP]*-1, FILTER(ALL('Date'),'Date'[Date] >=_min && 'Date'[Date] <= _day) )

Can you pls give me  QTR Previous EXP ?

Paul Yeo