Forum Discussion

Baskar's avatar
Baskar
Icon for Resident Rockstar rankResident Rockstar
8 years ago
Solved

Previous Month from NOW() or Today()

Thanks Advanced,

 

I have to get Previous month from NOW() or TODAY().

 

While using DATEADD or PREVIOUSMONTH it was asking Dates as columns. It won't allow this two functions.

 

Greg_Deckler 

Need Help

14 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    8 years ago

    How about:

     

    Measure = DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()))

    ?

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      In my oppinion what Greg_Deckler  proposed does not work correctly because you have to deal with dates like 29 of February, or even sympler what if today is 31st of something but one month ago 31st does not exist. Or what heppens if themonth is January?

      I do not have a solution yet but thsi is definately not the answer.

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Neither of the proposed solutions works fully due to the effects identified by Anonymous i.e. if the previous month doesn't contain the same 'day' value as 'today'. However, in my case, I needed to handle a change of year scenario but wasn't bothered about the actual 'day' value. I just wanted a measure to show the previous month/year in a card visual along the lines of 'January 2019' or 'June 2018' etc. This made it easy for me to still use the code to handle the year change with a little bit of change where the 'day' value is created - I just force it to always be a '1'.

         

        _monthYearTitle = 
    IF (
        MONTH ( TODAY () ) = 1,
        /* YES */
        FORMAT( DATE ( YEAR ( TODAY () ) - 1, 12, 1 ),"MMMM YYYY"),
        /* NO */
        FORMAT( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, 1 ), "MMMM YYYY")
    )
    • lilas153's avatar
      lilas153
      Regular Visitor
      4 years ago

      This will not give you last month's date for months with no day 31 or Feb 29 

    • divsforeal's avatar
      divsforeal
      Frequent Visitor
      4 years ago

      Thank you Greg for the above measure. I was trying so hard to get all dates until Last month (when we are dealing with future dates in date table)

       

      Thanks a lot! 🙂

  • anmattos's avatar
    anmattos
    Icon for Advocate I rankAdvocate I
    4 years ago

    Baskar,

     

    I had the same problem today. I just used the function EDATE, like that:

     

    EDATE(<start_date>, <months>)

     

    EDATE ( TODAY(), -1)

     

    It handles special cases like the previous month not having the 31st that, for example.

    • Mrtnkbg's avatar
      Mrtnkbg
      Icon for Advocate I rankAdvocate I
      2 years ago

      This solved my issue.

       

      I was looking for a solution to creating a measure for a text title that should contain the name of last/previous month from the current date via concatenate.

  • tsi-kah's avatar
    tsi-kah
    New Member
    7 years ago

    How will this return prior year if current month is January?

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Here, this should do the trick.

       

      Measure =
      IF (
      MONTH ( TODAY () ) = 1,
      DATE ( YEAR ( TODAY () ) - 1, 12, DAY ( TODAY () ) ),
      DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, DAY ( TODAY () ) )
      )

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi Baskar,

    The formula Greg_Deckler posted works correctly after test, please mark it as answer, more people will benefit from here. Today is 2018/1/16, you will expected result as follows.



    Best Regards,
    Angelia

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    We can use this for Dropdown to show the specific months

    [Index(Calendar.MonthsLong(),Month(Today())),Index(Calendar.MonthsLong(),Month(Today())-1)]