Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Create Latest and previous month marker

Hi

 

I'm trying to solve what i think should be a pretty simple problem, but it's beating me!

 

I'm trying to create a calculated column which basically returns whether a date is the "latest" month or it's the "previous" month.

 

I have the below code which doesn't work because the previousmonth function needs to reference a column. Does anyone have any ideas how to work around this??

 

if(Date_table[Month_end] = MAX(Date_table[Month_end]), "Latest", if(Date_table[Month_end] = PREVIOUSMONTH(MAX(Date_table[Month_end])), "Previous","NA"))
Need Help
  • Manoj_Nair's avatar
    Manoj_Nair
    3 years ago

    Anonymous 

    Try this code - I just tweaked your original code, but you need to add one more calculated column called PrevMonth. Check this out.

    1. 

     

    Prev Month = PREVIOUSMONTH(Date_table[Month_end])

     

    2.

     

    Month Marker = if(Date_table[Month_end] = MAX(Date_table[Month_end]), "Latest", if(Date_table[Month_end] = max(Date_table[Prev Month]), "Previous","NA"))

     

     

    Let me know if this works for you. Many Thanks

     

     

6 Replies

  • sudhav's avatar
    sudhav
    Icon for Helper V rankHelper V
    3 years ago
    Column = IF(MONTH(calender[Date])=MONTH(TODAY()),"Latest","Previous")
     
    is this you are excepting???
     
     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Unfortunately i can't use the today function as the dates i'm using are month end dates. So for instance my latest dates in the column are currently 31/12/2022 and 30/11/2022. I would like to mark the December date as "Latest" and the November date as "Previous"

      • sudhav's avatar
        sudhav
        Icon for Helper V rankHelper V
        3 years ago

        Provide some sample data(by removing sensitive data), so that its easy to get betetr answers..

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Manoj_Nair  many thanks. I'd actually tried this previously and it didn't work, but clearly i'd made a mistake somewhere and on second go it works perfectly!