Date filter to load previous month if it is the first of the new month

Power Query
1

I load tables based on the current month thru yesterday. On the first of the month, I need to load the entire previous month because we look at data in the month thru the previous day.

Is there a way to use an if statement that says if day of the current month = 1, use Date.IsInPreviousMonth([Date]) else

= Table.SelectRows(#“Changed Type”, each
Date.IsInCurrentMonth([Date]) and [Date] < Date.From(DateTime.LocalNow()))

2

Hi @LASHBURN,

I feel you where so close to solving it…
See if this works for you. I used this Date table M code and named that query fxCalendar, next add a new blank query and paste in the code below.

let
    Source = fxCalendar(#date(2017, 1, 1), #date(2022, 12, 31), null, null, null, null),
    Today = #date(2021, 2, 1), //Date.From(DateTime.LocalNow()),
    FilterDates = Table.SelectRows( Source, each (if Date.Day(Today)=1 then Date.IsInPreviousMonth([Date]) else Date.IsInCurrentMonth([Date]) and [Date] < Today ))
in
    FilterDates

I hope this is helpful.

3

Thanks Melissa for responding. I’ve done what you posted. I am unclear on how it will work. Do I need to refilter my tables to use this or is this some kind of global query for the report that will be used?

4

Hi @LASHBURN,

No it’s just so you can see the code in action… when that works you can transfer code from the FilterDates step to your query.

Today = #date(2021, 2, 1) // should get you all dates in the LM
Today = Date.From(DateTime.LocalNow()) // get’s you CM up to yesterday

5

I am not very familiar with M Code. . I pasted let
Source = fxCalendar(#date(2017, 1, 1), #date(2022, 12, 31), null, null, null, null),
Today = #date(2021, 2, 1), //Date.From(DateTime.LocalNow()),
FilterDates = Table.SelectRows( Source, each (if Date.Day(Today)=1 then Date.IsInPreviousMonth([Date]) else Date.IsInCurrentMonth([Date]) and [Date] < Today ))
in
FilterDates

as a step to filter on my table. I also tried just using FilterDates and get We cannot convert the value “FilterDates” to type Table.

6

Hi @LASHBURN,

No worries, I’ve attached a working sample for you.
eDNA - Filter Dates.pbix (117.6 KB)

7

So it looks like all I have to do is capture todays date as Today and it will work.

8

Yeah just remove this part #date(2021, 2, 1) // from the Today variable, that was just there to mimic a first of the month.

9

It works!! Thanks so much!

1 Like