For Loop with increasing date

[cbaumg]

Basically, my issue is that I want to loop through my entire dataset and see if my current date is between two other dates (both dates are columns in the dataset) and keep track of which ones are and which ones are not. I want to loop through and check the past thirty days. The dataset updates daily through a remote server so I want the 30 day range to start at the current day and go 30 days into the past and check the date ranges. 

I have tried creating columns, but they only allow me to look at one day at a time and I cannot use the visual that my company wants and has been done using Excel. Each row has a lot of info, but the only columns we care about are the Queue start date, Queue leave date, and the stage of the process. Eventually, I want the sum of each stage on these 30 dates.

[v-jingzhang · Accepted solution]

Hi @cbaumg 

If you only want to keep the data of previous 30 days in the data model, you can filter rows in Power Query Editor. Expand the down arrow on the date column header and select Date Filters > In the Previous..., enter 30 days in the Filter Row dialogue. In this way, it starts from yesterday's data without today's data. 

If you want to keep the current day as the start of previous 30 days, copy and paste below code into the formula bar.

= Table.SelectRows(PreviousStepName, each [Date] <= Date.From(DateTime.LocalNow()) and [Date] > Date.AddDays(Date.From(DateTime.LocalNow()),-30))

If you want to keep all data in the model but to show previous 30 days' data, you can apply a relative date filter on the date column in the report. Add a filter to a report in Power BI - Power BI | Microsoft Docs

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[parry2k]

@cbaumg Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490