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
  • Greg_Deckler's avatar
    Greg_Deckler
    8 years ago

    How about:

     

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

    ?

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")
    )
      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        I think the solution below solves the "different number of days in different month" and January issues. The main improvement on Anonymous's solution is taking the minimium of today's day and last month's last day using built in functions.

         

        Previous MTD = 

VAR start_of_prev_month = EOMONTH(TODAY(),-2) + 1

VAR mtd_prev_month = IF(MONTH(TODAY()) = 1,
    DATE(YEAR(TODAY())-1, MONTH(12), MIN(DAY(TODAY()), DAY(EOMONTH(start_of_prev_month,0)))),
    DATE(YEAR(TODAY()), MONTH(TODAY())-1,  MIN(DAY(TODAY()), DAY(EOMONTH(start_of_prev_month,0)))))


RETURN(CALCULATE(SUM(RET[Trade Quantity]),'Date'[Date] >= start_of_prev_month, 'Date'[Date] <= mtd_prev_month))
  • 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! 🙂

  • jyokela's avatar
    jyokela
    Frequent Visitor
    1 year ago

    This would not work for January because it would get December of current year, not of prior year.

  • JohnNPBIUser's avatar
    JohnNPBIUser
    Icon for Advocate I rankAdvocate I
    1 year ago

    Unfortuanely, I don't think this works correctly.  If you test this measure today (31/03/2025) then it gives a date of 03/03/2025, which is not the last month.  This is because the date function is calcualting the number of days in the previous month and subtracting that from today's month date so therefore the 31/03 - 28 = 03/03.