Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Creating a flag for previous month

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 fu...
Need Help
  • Stachu's avatar
    Stachu
    7 years ago

    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's avatar
    Nolock
    7 years ago

    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),
Stachu's avatar
Stachu
Community Champion
7 years ago

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)