Forum Discussion
Helper IIITrying to build Calculated Column for Current Month and Current Year
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?
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 _switch2. 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
10 Replies
ryan_mayuSuper User
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")))
ElvirBoticYes I have multiple years of data so it makes it harder for me.
- ryan_mayu
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.
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?
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 _switch2. 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