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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
thegekko42
Frequent Visitor

Is date in the last n days?

Hi

 

I have a Direct Query which give me a list of customers and the last date they placed an order.

I can then categegorise the customers into active customers and inactive customers, with active customers being customers that placed orders in the past 30 days.

 

I added a column with this formula:

if(Date.IsInPreviousNDays([LastOrderedDate],30)) then "Active" else "Inactive")

 

This formula works, but there's a definition issue as PreviousNDays is not the same as LastNDays.

PreviousNDays does not include today, so a customer that ordered today would look like an inactive customer 😞

 

I can't find any LastNDays formulas.

To get around that I tried a few variations of PreviuosNMinutes=43200 as that is as good as Live, but that formula doesn't work either 😞

 

I tried if it would help if I got the data from 30 days ago (Date.AddDays(DateTime.LocalNow() , -30)), but I couldn't match it to the LastOrderedDate without hitting Direct Query limitations.

 

So how do I get a column that shows Acitve/Inactive or even True/False based on the last 30 days?

I either find that my versions of the formulas doesn't exist, or that I hit the limitattions of Direct Query.

 

This is a snip of my data:

Forum_Picture.png

 

 

1 ACCEPTED SOLUTION

I managed to get to work

 

I did it in multiple steps, as every time I tried to combine multiple steps it didn't work.

 

I made a new column which just gave today's date:

Today=DateTime.Date(DateTime.LocalNow())

 

Then one more column to find the customers that were active in the previous 30 days:

Previous30=Date.IsInPreviousNDays([LastOrderedDate],30)

 

One more column to find the customers that were active today:

LastOrderedToday=[LastOrderedDate]=[Today]

 

And finally one to sum all that up:

Forum3.png

 

Which then gives me this:

Forum2.png

 

I would not call this an elegant solution, but it works 🙂

View solution in original post

5 REPLIES 5
jthomson
Solution Sage
Solution Sage

Rather than just jump straight to else inactive, add a second if statement asking if the date's today?

I did try that, but I couldn't make it work.

 

My issue was mainly that the M version of today() is DateTime.LocalNow() which is datetime and doesn't compare well with a date format column.

 

I do like the idea though, so I hope someone can help me get the formula for that

I managed to get to work

 

I did it in multiple steps, as every time I tried to combine multiple steps it didn't work.

 

I made a new column which just gave today's date:

Today=DateTime.Date(DateTime.LocalNow())

 

Then one more column to find the customers that were active in the previous 30 days:

Previous30=Date.IsInPreviousNDays([LastOrderedDate],30)

 

One more column to find the customers that were active today:

LastOrderedToday=[LastOrderedDate]=[Today]

 

And finally one to sum all that up:

Forum3.png

 

Which then gives me this:

Forum2.png

 

I would not call this an elegant solution, but it works 🙂

Hi @thegekko42,

 

It seems that you have solved your problem.

 

Please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

This thread may be old news. I ran into the same issue. The following seems to be working for me.  It selects rows where the difference in datetimezones is less than 90.  This will also return future dates, which would produce a negative difference. 

 

FilterMaxDatetoLast120Days = Table.SelectRows(AddMaxDateColumn, each Duration.Days(DateTimeZone.FixedLocalNow() - [MaxDate]) < 90),

 

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.