Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Baskar
Resident Rockstar
Resident Rockstar

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 

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

How about:

 

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

?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

12 REPLIES 12
Anonymous
Not applicable

We can use this for Dropdown to show the specific months

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

anmattos
Advocate I
Advocate I

@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.

tsi-kah
New Member

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

Anonymous
Not applicable

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
Employee
Employee

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.

1.PNG

Best Regards,
Angelia

Sorry to say but the solution posted by greg deckler does not work effeciently when dealing with year changes it is shocking to see a microsoft employee recommending someone to accept an incorrect answer.

Greg_Deckler
Super User
Super User

How about:

 

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

?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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! 🙂

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

Anonymous
Not applicable

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.

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
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.