Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

How to create expression for rolling amount start from next 12 month base on month end ?

Hi All

 

Below expression is rolling 12 month sales amount base on NOT month end working fine :-

 

_LAST12 = CALCULATE(sum(SALES[sales]),DATESINPERIOD('Date'[Date],MAX(SALES[date]),-12,MONTH))
 

Below expression rolling 12 month sales amount base on month end working fine :-

 

_LAST12_month end =
VAR _lastdate = MAX('Date'[Date])
RETURN
CALCULATE(sum(SALES[sales]),DATESINPERIOD('Date'[Date],_lastdate,-12,MONTH))
 
Below is rolling 12 month sales amount start from next 12 month NOT month end  working fine :-
 
_LAST24 = CALCULATE(SUM(SALES[sales]), DATESINPERIOD('Date'[Date], maxx('Date', DATEADD('Date'[Date],-12,MONTH)),-12, MONTH))
 

How to create expression rolling 12 month sales amount start from next 12 month base on month end working fine :-

 

_LAST24_month end =
VAR _lastdate = MAX('Date'[Date])
RETURN
CALCULATE(SUM(SALES[sales]), DATESINPERIOD('Date'[Date], maxx('Date', DATEADD('Date'[Date],-12,MONTH)),-12, MONTH))
 
How to make the above blue color code work ? 
 
Paul
 
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    @Paulyeo11 , Test as

    _LAST24_month final de la _LAST24_month
    VAR _lastdate á MAX('Fecha'[Fecha])
    var _min á date(year(_lastdate), month(_lastdate)-12, day(_lastdate))
    usar var _min á eomonth(date(year(_lastdate), month(_lastdate)-12, day(_lastdate)) ,0)
    devolución
    CALCULATE(SUM(SALES[sales]), DATESINPERIOD('Date'[Date], _min,-12, MONTH))

1 Reply

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

    @Paulyeo11 , Test as

    _LAST24_month final de la _LAST24_month
    VAR _lastdate á MAX('Fecha'[Fecha])
    var _min á date(year(_lastdate), month(_lastdate)-12, day(_lastdate))
    usar var _min á eomonth(date(year(_lastdate), month(_lastdate)-12, day(_lastdate)) ,0)
    devolución
    CALCULATE(SUM(SALES[sales]), DATESINPERIOD('Date'[Date], _min,-12, MONTH))