Get the value for the second last month in the dataset

[akhaliq7]

Need to get the value of the second last month in the dataset. The last date does not correspond to todays date so cannot use today but can use the max date - one month. I have tried lots of functions getting closest with the datediff and datesinperiod functions.

I have got the code for the last month in my dataset.

This Month = 
CALCULATE(
    SUM('Sales'[Profit]),
    DATESINPERIOD(
        'Date'[Date],
        MAX('Date'[Date]),
        -1,
        MONTH
    )
)

The above works but when I add a -2 for the month before this I am getting the sum of the last month and the month before just want the individual value. 

[tamerj1 · Accepted solution]

Hi @akhaliq7 
Best practice is to always have a YearMonthNumber column in the date table which is a sequential number that keeps adding 1 every month even over different years. If you don't have it then it can be simply created using

YearMonthNumber =
RANKX ( 'Date', FORMAT ( 'Date'[Date], "YYYYMM" ),, ASC, DENSE )

Then you can simply use

This Month =
CALCULATE (
    SUM ( 'Sales'[Profit] ),
    'Date'[YearMonthNumber] = MAX ( 'Date'[YearMonthNumber] ) - 2,
    ALLSELECTED ( 'Date' )
)

[akhaliq7]

Thanks you have saved me so much time this is a good solution as it is dynamic as well.

[darkinvader_]

Hi @akhaliq7 ,
I have a written a measure for this please do check,
/*

Second Last Month =

VAR _index = 2

VAR _StartMonth = DATE( YEAR(TODAY()), MONTH(Today()) - _index , 1)

VAR _StartNextMonth = EOMONTH( _StartMonth, 0) + 1

VAR _Profit = CALCULATE( SUM(Sales[Profit]), Date[Date] >= _StartMonth, Date[Date] < _StartNextMonth)

RETURN _Profit

*/

You can simply change the index to get any last/future month as well as you can even set _StartMonth as dynamic by using SelectedValue() function and create a Date.
Hope it solves your problem, do gives a thumbs if it does