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?

[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