Trying to build Calculated Column for Current Month and Current Year

[ElvirBotic]

Hello, looking to build a calculated column that will based on the date table add a new column to show three different statuses Current Month, Previous Month, and then Future Month. So, naturally for August 2021 it should be "Current Month" then for Jan-July be "Previous Month" and anything after August 2021 (i.e. September 2021) "Future Month". My intention is to be able to quickly slice my report with a few selections to show results for previous months because viewers may not want to see current month until the month ends, but still have the ability to show current month if needed. 

I have tried using multiple IF statements, but I am thinking I may need to use variables and just do a lot of comparing. Any help/Suggestions?

[v-yangliu-msft · Accepted solution]

Hi  @ElvirBotic ,

Here are the steps you can follow：

1. Create calculated column.

Switch =
var _currentyear=YEAR(TODAY())
var _currentmonth=MONTH(TODAY())
var _maxcurrentmonthday=EOMONTH(TODAY(),0)
var _maxlastmonth=EOMONTH(TODAY(),-2)
var _switch=
SWITCH(
    TRUE(),
    YEAR('Table'[Date])=_currentyear&&MONTH('Table'[Date])=_currentmonth,"Current Month",
    YEAR('Table'[Date])=_currentyear&&MONTH('Table'[Date])=_currentmonth-1,"Previous Month",
    'Table'[Date]<=_maxlastmonth,"Previous",
    'Table'[Date]>_maxcurrentmonthday,"Future Month")
return
_switch

2. Result:

Filter the data to extract representative dates to better view the results

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[v-yangliu-msft]

Hi  @ElvirBotic ,

Are you saying that you want to label blank values.

Here are the steps you can follow：

1. Create calculated column.

Column =
var _currentyear=YEAR(TODAY())
var _currentmonth=MONTH(TODAY())
var _switch=
SWITCH(
    TRUE(),
MONTH('Table'[Date])=_currentmonth&&YEAR('Table'[Date])=_currentyear,"Current Month",
'Table'[Date] <DATE(YEAR(TODAY()),MONTH(TODAY()),1),"Previous Month",
MONTH('Table'[Date])>_currentmonth&&YEAR('Table'[Date])>=_currentyear,"Future Month")
return
IF('Table'[Date]=BLANK(),"blank",_switch)

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[ElvirBotic]

This would work for one year of data, but I have multiple years of data is there a way for fix to include 2020, 2019, etc?

[ElvirBotic]

Yes I have multiple years of data so it makes it harder for me.

[ryan_mayu]

@ElvirBotic 

you can try this

Column = if(year('date'[Date])=year(today())&&month('date'[Date])=month(today()),"Current Month",if(year('date'[Date])=year(today())&&month('date'[Date])<month(today()),"Previous Month",if('date'[Date]>EOMONTH(today(),0),"Future Month")))

pls see the attachment below.

[ElvirBotic]

I saw that for 2020 there are a lot of blank values. Is there a way to avoid this? Essentially every date for the date dimension should have a previous, current, and future record. 

[ryan_mayu]

@ElvirBotic 

so what do you want to display for past years, you didn't mention that in your description.

[ElvirBotic]

Just "Previous" 

[ryan_mayu]

@ElvirBotic 

pls try this

Column = if(year('date'[Date])=year(today())&&month('date'[Date])=month(today()),"Current Month",if('date'[Date]<=EOMONTH(today(),-1),"Previous Month",if('date'[Date]>EOMONTH(today(),0),"Future Month")))

[ryan_mayu]

@ElvirBotic 

is this what you want? I assume you only have one year data in date table. if you have more than one year data, you need to modify the DAX

Column = if(MONTH('Date'[Date])=month(today()),"Current Month",if('Date'[Date]<=EOMONTH(today(),-1),"Previous Month",if('Date'[Date]>EOMONTH(today(),0),"Future Month")))