cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

New Member

## Query Last 100 Days for Past 3 Years

Greetings All,

I'm looking to set up a daily query that will pull the last 100 days from TODAY, for the current-year, prior-year and prior-prior-year periods.

The date ranges would look something like this from today's date December 7th:

 PPY PY CY 12/7/2021 12/7/2022 12/7/2023 8/29/2021 8/29/2022 8/29/2023

I'm using Power Bi Desktop and have a daily scheduled refresh set-up

The code below is what I use to pull the last 100 days each morning, but not sure how to set up for the prior year periods.

= Table.SelectRows(#"Removed Columns", each Date.IsInPreviousNDays([close_date], 100))

1 ACCEPTED SOLUTION
Super User

Construct the periods by yourself (take 5 days for example)

``````let
Source = let today=Date.From(DateTime.LocalNow()) in List.Accumulate({0..2}, {}, (s,c) => s & List.Dates(Date.AddYears(today, -c), 5, #duration(-1,0,0,0)))
in
Source``````

 Expertise = List.Accumulate(        {Days as from Today},        {Skills and Knowledge},        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday))

2 REPLIES 2
Super User

Construct the periods by yourself (take 5 days for example)

``````let
Source = let today=Date.From(DateTime.LocalNow()) in List.Accumulate({0..2}, {}, (s,c) => s & List.Dates(Date.AddYears(today, -c), 5, #duration(-1,0,0,0)))
in
Source``````

 Expertise = List.Accumulate(        {Days as from Today},        {Skills and Knowledge},        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday))

New Member

This worked great. Thanks a lot, ThxAlot