Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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 🙂

View solution in original post

Nolock
Resident Rockstar
Resident Rockstar

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 🙂

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

Nolock
Resident Rockstar
Resident Rockstar

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors