Previous month and Current month custom Date columns

[FlightofICARUS]

Hi everyone,

I am struggling to create a custom column to indicate "Previous Month" for my Date Table.

I've come as far as creating a Current Month with a custom column in M-code: 
if [Date] >= [Start_Of_This_Month] and [Date] <= [#"End_Of_Current_Month "] then 1 else 0

Here's a Dropbox link (https://www.dropbox.com/sh/r5fe8i7ej720jnn/AADsipGqJ1f1F7J7O4QZUoPTa?dl=0) to the PBIX file with the Calendar I need help with, if someone can either direct me in the right direction or edit my template that would be much appreciated.

The end Result should be one column with 3 options(In text);

"Current Month" = Current Month to Current Month End of Month (ex 01June - 30 June)

"Previous Month" = Previous month, StartOfMonth - Previous Month, EndOfMonth (ex 01 May- 31May)

"Historical" = All dates prior to "Previous Month"

If it is easier in DAX that should also be fine as long as I can have a slicer on my Report PAge view To select those 3 options in one slicer.

In DAX I have a MTD/QTD/YTD Multiple selection which might also be the way to go to achieve above mentioned.

 Here is the code to edit if needed

MTD/QTD/YTD Selection =

VAR TodayDate = TODAY()

VAR YearStart = CALCULATE(STARTOFYEAR(FY_Calendar[Date]), YEAR(FY_Calendar[Date]) = YEAR(TodayDate))

VAR QuarterStart = CALCULATE(STARTOFQUARTER(FY_Calendar[Date]), YEAR(FY_Calendar[Date]) = YEAR(TodayDate),QUARTER(FY_Calendar[Date]) = QUARTER(TodayDate))

VAR MonthStart = CALCULATE(STARTOFMONTH(FY_Calendar[Date]),YEAR(FY_Calendar[Date]) = YEAR(TodayDate), MONTH(FY_Calendar[Date]) = MONTH(TodayDate))

VAR Result =

UNION(

ADDCOLUMNS(

CALENDAR(YearStart, TodayDate),

"Selection" , "YTD"

),

ADDCOLUMNS(

CALENDAR(QuarterStart,TodayDate),

"Selection","QTD"

),

ADDCOLUMNS(

CALENDAR(MonthStart,TodayDate),

"Selection", "MTD"))

RETURN Result

Thank you in advance to anyone willing to help 🙂

[amitchandak · Accepted solution]

@FlightofICARUS ,

Not very clear. Try a new column

Month Type = Switch( True(),
eomonth([Date],0) = eomonth(Today(),-1*month(Today())),"Last year Last Month" ,
eomonth([Date],0) = eomonth(Today(),-1),"Last Month" ,
eomonth([Date],0)= eomonth(Today(),0),"This Month" ,
Format([Date],"MMM-YYYY")
)

[FlightofICARUS]

@amitchandakthank you sir, that worked 100%. Thank you for your response I appreciate it.

Do you mind showing me how to do this in PQ to broaden my understanding based on columns in Date Dimension below?