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)

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

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