Forum Discussion

RichJW's avatar
RichJW
Icon for Helper III rankHelper III
3 years ago
Solved

Date Field for Next x Month

Hi all,   I'd like to create a new column in PBI Desktop which will look at the "DateField" column (date format) I have and split it into categories for filtering purposes. So, I'd have a "Next 3 ...
Need Help
  • Arul's avatar
    Arul
    3 years ago

    RichJW ,

    Check if it is working or not in a calculated column,

     

    Slicer = 
VAR _today = TODAY()
VAR _today90 = TODAY()+90
VAR _today180 = TODAY()+180
VAR _today210 = TODAY()+210
RETURN SWITCH(TRUE(),
    'Date'[Date]>=_today && 'Date'[Date]<=_today90,"Next three months",
    'Date'[Date]>=_today90 && 'Date'[Date]<=_today180,"3-6 months",
    'Date'[Date]>=_today180 && 'Date'[Date]<=_today210,"6-9 months",
    'Date'[Date]<_today,"Previous Months",
    "Greater than 9 Months"
    )

     

     

    Thanks,

    Arul

RichJW's avatar
RichJW
Icon for Helper III rankHelper III
3 years ago

Hi Arul 

Today's date, please.

 

Thanks,

Rich

Arul's avatar
Arul
Icon for Super User rankSuper User
3 years ago

RichJW ,

Check if it is working or not in a calculated column,

 

Slicer = 
VAR _today = TODAY()
VAR _today90 = TODAY()+90
VAR _today180 = TODAY()+180
VAR _today210 = TODAY()+210
RETURN SWITCH(TRUE(),
    'Date'[Date]>=_today && 'Date'[Date]<=_today90,"Next three months",
    'Date'[Date]>=_today90 && 'Date'[Date]<=_today180,"3-6 months",
    'Date'[Date]>=_today180 && 'Date'[Date]<=_today210,"6-9 months",
    'Date'[Date]<_today,"Previous Months",
    "Greater than 9 Months"
    )

 

 

Thanks,

Arul

  • RichJW's avatar
    RichJW
    Icon for Helper III rankHelper III
    3 years ago

    Hi Arul,

     

    Thank you, that is fantastic. The only thing it has brought up is the "Greater than 9 Months" also includes dates in the past, which I was hoping to ignore.

    However, I've added and edited one of your lines of code to show "'Date'[Date]<_today && 'Date'[Date]<_today,"Before Today"," - and this works perfectly.

     

    Many thanks and kudos to you.

    Rich