Forum Discussion

admin11's avatar
admin11
Icon for Memorable Member rankMemorable Member
4 years ago
Solved

How to get Current Quarter -1 Exp Expression ?

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
 
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    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) )

     

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      admin11
      Icon for Memorable Member rankMemorable Member
      4 years ago

      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