Help with M Code

[H_insight]

Hi All,

I had a sample file (attached) that contains a Data table, which consist of 17 rows and 2 columns. I would like to add an M code (Not Dax) in power query to add a conditional colmun where:

1- Date column must be in the last 3 months; And

2- Active column = Y; And

3- User Column = N

Please see below screenshot of what to expect in "New Column".

Sample File

Many thanks

H

[Greg_Deckler · Accepted solution]

@H_insight Try:

if [Date] > Date.AddMonths(Date.From(DateTime.LocalNow()),-3) and [Active column] = "Y" and [User Column] = "N" then 1 else 0

[BA_Pete · Accepted solution]

Hi @H_insight ,

Try this:

if [Date] >= Date.AddMonths(Date.From(DateTime.LocalNow()), -3)
  and [Active / Not] = "Y"
  and [User/Consumer] = "N"
then 1
else 0

This uses three rolling months i.e. today it will give you 2021-08-08 to 2021-11-08.

If you want it to evaluate against the last three complete months i.e. not including current month, then it would be:

if Date.IsInPreviousNMonths([Date], 3)
  and [Active / Not] = "Y"
  and [User/Consumer] = "N"
then 1
else 0

Pete

[H_insight]

Thank you @BA_Pete for the prompt response. This is exactly what I was looking for.

[BA_Pete]

@H_insight ,

Glad to hear it.

Just out of interest, did you use the rolling three months or the three complete months?

Pete

[H_insight]

@BA_Pete I Have used the rolling 3 months.

[Greg_Deckler · Accepted solution]

@H_insight Try:

if [Date] > Date.AddMonths(Date.From(DateTime.LocalNow()),-3) and [Active column] = "Y" and [User Column] = "N" then 1 else 0