Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
akhaliq7
Continued Contributor
Continued Contributor

Get the value for the second last month in the dataset

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. 

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

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

 

View solution in original post

3 REPLIES 3
tamerj1
Super User
Super User

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
Continued Contributor
Continued Contributor

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

darkinvader_
Frequent Visitor

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors