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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Powell360
Helper I
Helper I

First day of previous month as type Date

HI all,

 

I need to get the 1st day of last month (based on the current calendar month) into a measure but I have hit some confusion.

 

I started with,

 FOM = DATE(YEAR(TODAY()), MONTH(TODAY()), 1)

and tried to wrap a DATEADD around it to decrement the month,

FOM = DATEADD((DATE(YEAR(TODAY()), MONTH(TODAY()), 1)), -1, MONTH)

the problem is that when I do that I get the error "The first argument do 'DATEADD' must specify a column but I have seen examples others have used when an expression or function has been passed in as the date. Can anyone see where I am going wrong or suggest another approach?

 

Thanks

 

2 ACCEPTED SOLUTIONS

Not really, I'm just considering the case that'll happen about 4 weeks from now where the measure that I've suggested will try to return a month of 0 and break. I've just plugged that suggestion into a measure and it's returning November 1st right now, so the whole thing should look something like:

 

PreviousMonthStart = if(month(today())=1,date(year(today())-1,12,1),date(year(today()),month(today())-1,1))

 

Might be missing a bracket somewhere

View solution in original post

fabianbram
Advocate II
Advocate II

Can you try this? It worked for me... From what I understand you wanted the first day of last month. You can use this metric to get that:

 

FIRSTDAYOFMONTH = EOMONTH(TODAY(),-2)+1

View solution in original post

7 REPLIES 7
fabianbram
Advocate II
Advocate II

Can you try this? It worked for me... From what I understand you wanted the first day of last month. You can use this metric to get that:

 

FIRSTDAYOFMONTH = EOMONTH(TODAY(),-2)+1

@fabianbram that is a much more elegant solution and it should handle the year chenage easily becasue they are all date based functions. Thank you very much 

Your welcome!

 

jthomson
Solution Sage
Solution Sage

Could you not try to just add a -1 after MONTH(TODAY()) into the first measure you listed, and then if that's working for anything that's not January, put in an if statement to handle that case and take one off the year instead while making the month December?

@jthomson Thanks very much for the reply.

 

 

I am still quite new to Power BI, DAX and M and I have had trouble writing the custom elements. Am I right in saying that for a measure I would need the IF which is used in DAX? 

 

Thanks

 

 

 

Not really, I'm just considering the case that'll happen about 4 weeks from now where the measure that I've suggested will try to return a month of 0 and break. I've just plugged that suggestion into a measure and it's returning November 1st right now, so the whole thing should look something like:

 

PreviousMonthStart = if(month(today())=1,date(year(today())-1,12,1),date(year(today()),month(today())-1,1))

 

Might be missing a bracket somewhere

I have just seen your reply. Thanks very much, I have written the below and I guess I'll know if it can handle a year change properly in a few weeks!

FOM = 
	IF(MONTH(TODAY()) <> 1,
	FORMAT(DATE(YEAR(TODAY()), MONTH(TODAY())-1, 1),"DD/MM/YYYY"),
	FORMAT(DATE(YEAR(TODAY())-1, 12, 1),"DD/MM/YYYY")
	)

 

Thanks very much 

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.