How to filter dates between a certain number of days?

[RedHare]

I have a table that includes a date column. I need to split this table into 3 seperate tables which filter that date column between dates that occured in the past 0-30, 31-60, and 61-90 days. 
Initially I was thinking of using the Date.IsInPreviousNDays function, but how can I say is in the previous 31-60 days?

Thanks!

[mahenkj2]

Hello@RedHare 

As @speedramps suggested, better to add transformation in the date table and relate that table with the fact table.

You can add a custom column to count days from today:

Then add a conditional column to categorize the datefilter:

The date table used is not the proper one, just created a short sample.

Please see the sample file on link below:

https://drive.google.com/drive/folders/1JjSs9JRF2WPjlLHHCOoV5kzij4zqUzhw?usp=sharing

[Vijay_A_Verma · Accepted solution]

I am presuming you want a PQ solution as you have posted in PQ forum and you talked about Date.IsInPreviousNDays.

Below are the formulas for these in PQ which will help you to apply required filter

For within 30 days
= Date.IsInPreviousNDays([Date],30)

For 31-60 days bin
= Date.IsInPreviousNDays([Date],60) and not Date.IsInPreviousNDays([Date],30)

For 61-90 days bin
= Date.IsInPreviousNDays([Date],90) and not Date.IsInPreviousNDays([Date],60)

[RedHare]

Thank you, this is exactly what I needed.

[speedramps]

Hi RedHare

It is bad praticve to build your own date logic,

Always use calenedar table for date logic.

Build and test it once and use it always !

Then you can slice and subtotal by date, week, month and year age.

You dont have to split the tables, unless you want to.

You could have all the dates in one fact table with a calendar filter to produce 3 reports.

Click here to watch video 

Please click the thumbs up and accept as solution buttons