cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dkpcr5
Helper I
Helper I

M Query - January 1st of Previous Year

Hi all,

I'm looking for an M Query assist.

I'd like to set my StartDate to January 1st of the previous year, and my EndDate to the current day. So, start date should read 01-01-2020 and EndDate should read (today, so 09-23-2021). How would I cast that in M Query?

 

let StartDate = ...?

let EndDate = Date.From(DateTime.LocalNow()) ...?

 

Thank you!

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@dkpcr5 

let EndDate = Date.From(DateTime.LocalNow())
let StartDate = #date(Date.Year(EndDate)-1,1,1)

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

View solution in original post

2 REPLIES 2
AlexisOlson
Super User
Super User

Another way would be to use the Date.StartOfYear and Date.AddYears functions.

 

StartDate = Date.From(Date.StartOfYear(Date.AddYears(DateTime.LocalNow(), -1)))

Greg_Deckler
Super User
Super User

@dkpcr5 

let EndDate = Date.From(DateTime.LocalNow())
let StartDate = #date(Date.Year(EndDate)-1,1,1)

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Kudoed Authors