cancel
Showing results for
Did you mean:
Resident Rockstar

## Previous Month from NOW() or Today()

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.

1 ACCEPTED SOLUTION
Super User IV

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

?

---------------------------------------

@ me in replies or I'll lose your thread!!!

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

7 REPLIES 7
New Member

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

Regular Visitor

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 () ) )
)

Microsoft

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

Super User IV

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

?

---------------------------------------

@ me in replies or I'll lose your thread!!!

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Resolver I

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.

Frequent Visitor

Neither of the proposed solutions works fully due to the effects identified by @tschris 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")
)```
New Member

I think the solution below solves the "different number of days in different month" and January issues. The main improvement on @amaclure'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))``````

Announcements

#### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

#### Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

#### Claim Your Badge & Digital Swag!

Check out how to claim yours today!

#### Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors
Top Kudoed Authors