Power query M - how to define "this year" and "last year"

[StephenF]

Hi,

I'm making a custom column and what it populated with 2020 and another with 2019.

How do I define this year and last year in this language?

[Rickmaurinus]

You could go about this in several ways. Either you can retrieve the current datetime value, extract the year of it and then use it to filter your dates: 

if Date.Year( [DateColumn] ) = Date.Year( DateTime.LocalNow() ) then "Current Year" else if Date.Year( [DateColumn] ) = Date.Year( DateTime.LocalNow() ) - 1 then "Prior Year" else "Other periods " 

 On the other hand, if you want to make use of Power Query's build in date information functions, you can use: 

if Date.IsInCurrentYear( [DateColumn] ) then "Current Year" else if Date.IsInPreviousYear( [DateColumn] ) then "Prior Year" else "Other"

For some clear screenshots of the cutoff dates, check out: 

https://powerquery.how/date-isinpreviousyear/

https://powerquery.how/date-isincurrentyear/

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

@ me in replies or I'll lose your thread

Master Power Query M? -> https://powerquery.how

Read in-depth articles? -> BI Gorilla

Youtube Channel: BI Gorilla

If this post helps, then please consider accepting it as the solution to help other members find it more quickly.

[bakerm00]

since i've gone thru the pain of learning this today. 

determines if an end date is in this year or next (for a gantt visual filter)

= Table.AddColumn(#"convert progress to percent", "ganttyearfilter", each if([Forecast End Date] is null) then "0" else if(Date.IsInCurrentYear([Forecast End Date])) then "1" else if(Date.IsInNextYear([Forecast End Date]))then "2" else "99")

[littlemojopuppy]

Question: why define separate columns for this in Power Query when you can easily accomplish the same in DAX?

But if you have to, there are M functions to accomplish this...

[StephenF]

Because I had this at the report level. I want it at the data source "edit query" level so I dont need to write a new formula for every report the data source references.

I've searched for half an hour on google and here and found nothing. The M documentation is not so good.

[littlemojopuppy]

Are you trying to accomplish a year over year comparison?

[StephenF]

No, Im creating a filter to limit data to this year and last year.

[littlemojopuppy]

Oversimplifying, but you could simply have columns for year and amount and filter for Date.IsInPreviousYear or Date.IsInCurrentYear.  Having amounts in different fields could make measure writing later on more complicated than it needs to be

[StephenF · Accepted solution]

I'm going to go with

if Date.Year(DateTime.LocalNow()) = ( [InvPayment_Due_Year] ) or Date.Year(DateTime.LocalNow()) = ( [InvPayment_Due_Year]+1 ) then 1 else 0