YTD month flag based off current month (flag all prior months)

[Anonymous]

Hello,  I have a regular date table and having a hard time figured out a way to add a column to the data table that simply says "YTD" if the month in the table is less than the current month.  For example, if if the current month were to be June, I would like all the rows for the months of January through May to say "YTD" in the new column.  I need a column that does specifically this for many reasons, so I'd really appreciate this specific solution and not 100 different work arounds.  🙂  Thanks so much in advance!  

[PaulDBrown · Accepted solution]

@Anonymous 

If you only want the flag for the current year:

YTD = IF(date table [month] < MONTH(TODAY()) && date table [year] = YEAR(TODAY()), "YTD", BLANK())

[Anonymous]

Thanks all!  I feel really stupid.  🙂

[Greg_Deckler]

So, I'm thinking:

YTD = 
  IF(
    MONTH([Date]) < MONTH(TODAY()),
    "YTD",
    BLANK()
  )

Of cource you did not mention years so not sure if that is an issue or not, but that too would be possible. 

[Anonymous]

@Greg_DecklerThanks!  This did exactly what I was looking for...however, I found that our data does not refresh for the prior month until the 4th or 5th business day of the current month.  Therefore, I would now like to adjust the YTD flag formula to take the max period from the current year in my data (instead of month(today)) and use that, so that we're always comparing apples to apples across years.  I hope that makes sense and REALLY appreciate your help!!!