Creating a flag for previous month

[Anonymous]

Hi, 

Im trying to create a flag for when a provider is new in the previous month. I want the previous months data to have a 1 and everything else to have a 0.

I'm struggling to find a date function that will automatically update for when the file is being created. The file has data in from previous years so it needs to be one that doesn't include data from the previous years. 

Currently I have:

#"Added Conditional Column" = Table.AddColumn(#"Sorted Rows", "Provider is new last month", each if [Provider start date] > #date(2019, 4, 1) then 1 else 0),

I want to make it so the date(2019, 4, 1) part of the formula is not manual. 

I have looked at the previous month function and it looks like the right thing to use but I can't get it to work! 

Thanks in advance!

Tanya

[Stachu · Accepted solution]

the code I posted can only replace 4 in your example, try this:

#"Added Conditional Column" = Table.AddColumn(#"Sorted Rows", "Provider is new last month", each if [Provider start date] > #date(Date.Year(Date.StartOfMonth(DateTime.FixedLocalNow())-#duration(1,0,0,0)), Date.Month(Date.StartOfMonth(DateTime.FixedLocalNow())-#duration(1,0,0,0)), 1) then 1 else 0),

[Stachu]

try something like this in place of 2019 and 4 from your code

Date.Month(DateTime.FixedLocalNow())
Date.Year(DateTime.FixedLocalNow())

EDIT
something like this is probably better as it handles the end of year as well

Date.Month(Date.StartOfMonth(DateTime.FixedLocalNow())-#duration(1,0,0,0))

it's basically a month (Date.Month) of the 1 day before (-#duration(1,0,0,0)) of the start of (Date.StartOfMonth) the current date (DateTime.FixedLocalNow)

[Anonymous]

@Stachu 

I found this link too but still can't seem to get any of it to work! 

https://docs.microsoft.com/en-us/powerquery-m/date-isinpreviousmonth

[Anonymous]

Hi,

thanks for your help.

I tried adding that formula and it hsa given me the error 'Token Literal expected'. Do you know why this is? Also, using this formula does it matter when the file is updated as this will vary each month.

#"Added Conditional Column" = Table.AddColumn(#"Sorted Rows", "Provider new last month", each if [Provider start date] > Date.Month(Date.StartOfMonth(DateTime.FixedLocalNow())-#duration(1,0,0,0)) then 1 else 0,

in
#"Added Conditional Column"

[Stachu · Accepted solution]

the code I posted can only replace 4 in your example, try this:

#"Added Conditional Column" = Table.AddColumn(#"Sorted Rows", "Provider is new last month", each if [Provider start date] > #date(Date.Year(Date.StartOfMonth(DateTime.FixedLocalNow())-#duration(1,0,0,0)), Date.Month(Date.StartOfMonth(DateTime.FixedLocalNow())-#duration(1,0,0,0)), 1) then 1 else 0),

[Nolock · Accepted solution]

Hi @Anonymous,

there are build-in date functions which you can use: Date.IsInPreviousMonth and Date.IsInCurrentMonth.

let
    DateToTest = #date(2019, 5, 5),
    Result = {
         Date.IsInPreviousMonth(DateToTest),
         Date.IsInCurrentMonth(DateToTest)
    }
in
    Result

Usage in your case:

#"Added Conditional Column" = Table.AddColumn(#"Sorted Rows", "Provider is new last month", each if Date.IsInPreviousMonth([Provider start date]) or Date.IsInCurrentMonth([Provider start date]) then 1 else 0),

or even shorter if you expect just a logical type (True/False as result):

#"Added Conditional Column" = Table.AddColumn(#"Sorted Rows", "Provider is new last month", each Date.IsInPreviousMonth([Provider start date]) or Date.IsInCurrentMonth([Provider start date]), type logical),