Last 12 Full (Complete) Months Measure

[Anonymous]

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.

RB

[jdbuchanan71 · Accepted solution]

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.

[Anonymous]

HI @jdbuchanan71 

Thx. It works.

I have another query relating to the use of today(). I am in Sydney, AUS and my report is hosted in the US. So there will be an issue with the Calculation when it is the 1st day of the month in Sydney. PowerBI server will be about 12 hrs behind. Can you help with this one. I have looked/searched but no one has a simple answer.

This is important as on the 1st day of the month, users like to see how the previous Month and 12Months Sales were.

Thanks.

[jdbuchanan71]

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]

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