Forum Discussion

ElvirBotic's avatar
ElvirBotic
Icon for Helper III rankHelper III
5 years ago
Solved

Trying 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...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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

ryan_mayu's avatar
ryan_mayu
Icon for Super User rankSuper User
5 years ago

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's avatar
ElvirBotic
Icon for Helper III rankHelper III
5 years ago

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's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    5 years ago

    ElvirBotic 

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

    • ElvirBotic's avatar
      ElvirBotic
      Icon for Helper III rankHelper III
      5 years ago

      Just "Previous" 

      • ryan_mayu's avatar
        ryan_mayu
        Icon for Super User rankSuper User
        5 years ago

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