Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Last 12 Full (Complete) Months Measure

Hi All,   I want to create a Measure that will calculate the Last 12 Full (Complete) Months Sales, ie ignores the Sales for the Current Month and Totals for the past 12 Months.    Pls help.   R...
Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    6 years ago

    Hell Anonymous 

    Give this a try

    Trailing 12 full months = 
VAR _T = TODAY()
VAR _End = EOMONTH( _t , -1 )
VAR _Start = EOMONTH( _t , -13 ) + 1
RETURN 
CALCULATE (
    SUM ( 'Table'[Amount] ),
    DATESBETWEEN( 'Table'[Date] , _Start , _End )
)

    You will have to change the table and column names to match yours.

jdbuchanan71's avatar
jdbuchanan71
Icon for Super User rankSuper User
6 years ago

Hello Anonymous 

We could change the measure like so.

Trailing 12 full months = 
VAR _T = NOW() + .5
VAR _End = EOMONTH( _t , -1 )
VAR _Start = EOMONTH( _t , -13 ) + 1
RETURN 
CALCULATE (
    SUM ( 'Table'[Amount] ),
    DATESBETWEEN( 'Table'[Date] , _Start , _End )
)

This will set _t to the local time + 12 hours (.5 = half a day: 1/24*12) and the rest of the formula will calculate from that.  Give that a try, see if it works for you.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

jdbuchanan71  Thx., i have put that in. Will find out on 1st of Nov. Thx again.