cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TanyaFozz
Helper I
Helper I

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 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

2 ACCEPTED SOLUTIONS
Stachu
Community Champion
Community Champion

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),


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

View solution in original post

Nolock
Memorable Member
Memorable Member

Hi @TanyaFozz,

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),

View solution in original post

5 REPLIES 5
Stachu
Community Champion
Community Champion

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)



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

@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

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
Community Champion
Community Champion

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),


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

Nolock
Memorable Member
Memorable Member

Hi @TanyaFozz,

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),

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!