Only show the last 24 months, starting from previous month

[kinder]

Hi Guys,

I wan't a measure or column that shows me the last 24 months, from last month.  So that will be March 2017 - March 2019.

The data set is simple

Sales data table,

Sales Number,

Value,

Date

Calendar

Year,

Month,

MonthYear

Feel free to add any fields in the Calendar to do the last 24 months, starting from previous month.

And the financial year starts in June.

Thanks

Lak

[judspud]

Hi @kinder 

You can create a calculated column in the calendar table to indicate whether the date is in your specified time period.

An if statement to check whether the date is greater than or equal to last month minus 2 years and less or equal to last month.

Then apply a report level filter to only include rows where the new column = True

Hope this helps

Thanks,

George

[kinder]

Thanks, but what is the syntax of that if statement.

[judspud]

@kinder Please could you share an extract of your Calendar table so i can see the format of each column?

Thanks,

George

[kinder]

You can create one from scratch to make it easier.

Or

Date =
ADDCOLUMNS (
CALENDAR (DATE(2018,1,1), DATE(2022,31,12)),
"DateAsInteger", FORMAT ( [Date],"YYYYDDMM" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )
)

[judspud · Accepted solution]

Thanks for this @kinder 

Please use this formula;

Last 2 Years = if(And('Date'[Date]>=date(year(today()),Month(today())-25,1),'Date'[Date]<=(date(year(today()),month(today()),1)-1)),TRUE(),FALSE())

Feel free to simplify this were possible as this is a rough and ready one that provides what you need.

You can then apply a report level filter to only display True values

Thanks,

George

[kinder]

That worked a treat. Thanks