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

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

Reply
1Kash
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:

 

PPYPYCY
12/7/202112/7/202212/7/2023
8/29/20218/29/20228/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))

 

Thanks in advance!

1 ACCEPTED SOLUTION
ThxAlot
Super User
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

ThxAlot_0-1702008033404.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday)


)



View solution in original post

2 REPLIES 2
ThxAlot
Super User
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

ThxAlot_0-1702008033404.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday)


)



This worked great. Thanks a lot, ThxAlot

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors