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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Power Query exclude dates in previous calendar month and current incomplete month

Hullo, 

I have an Azure dataset that pulls data from the previous calendar month, and current incomplete month. So as today is January 19th 2022 - the dataset holds December 2021 and the first half of January 2022. Next month, it will drop December 2021 and so forth.

I also have a data pull of historic Azure data which holds 12 calendar months. But, I need to EXCLUDE the previous calendar month so I don't duplicate volumes. I can't manually exclude December 2021, because next month I will need to exclude January 2022 and so forth. 

Please help!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @Anonymous ,

 

Add a custom column:

column = if Date.StartOfMonth([date]) > Date.From(Date.StartOfMonth(Date.AddMonths(DateTime.LocalNow(), -1))) then 1 else 0

1.PNG

Then filter the table by custom column.

 

Best Regards,

Jay

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

Hi @Anonymous ,

 

Add a custom column:

column = if Date.StartOfMonth([date]) > Date.From(Date.StartOfMonth(Date.AddMonths(DateTime.LocalNow(), -1))) then 1 else 0

1.PNG

Then filter the table by custom column.

 

Best Regards,

Jay

amitchandak
Super User
Super User

@Anonymous , Try to add filter like

= Table.SelectRows(#"Changed Type", each [Column1] < Date.StartOfMonth( Date.AddMonths(DateTime.Date(DateTime.LocalNow()),-1)))

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjbQ9csv0zUyMDJUitUB8g11XVKTEXxDfUN9IMdIKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Column1] < Date.StartOfMonth( Date.AddMonths(DateTime.Date(DateTime.LocalNow()),-1)))
in
    #"Filtered Rows"

 

example code 

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
freginier
Super User
Super User

Add new column with this formula 

https://docs.microsoft.com/en-us/powerquery-m/date-isinpreviousmonth

 

Then if is previous month then filter it 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.