filter

[Anonymous]

Hello,

I have data from Nov, dec, Jan, Feb, March....... I need to bring in data in PowerBI till previous month of the current date/month.

mean if current month is Feb, we need to filter data till Jan .

How can we filter the data at dataset level? Can we do it in query editor?

[Anonymous · Accepted solution]

Hi @Anonymous ,

Create a cutsom column as below.

if Date.Month([Column1]) < Date.Month(DateTime.LocalNow()) then 1 else 0

 

Then filter the data that custom is 1 and remove the custom column.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJUitWJVjJC5hjDObEA", 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}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Date.Month([Column1]) < Date.Month(DateTime.LocalNow()) then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"})
in
    #"Removed Columns"

Best Regards,

Jay

[Anand24]

Hi @Anonymous ,

You can simply apply filter with last N months. Power Query considers it as complete months and will provide you data till end of previous month.

Step 1: Go to Power Query Editor

Step 2: Apply "In the Previous" Date Filter

Note: Data here consists of Feb too.

Step 3: Put "n" month according to the data you have. I have taken 100 months for example.

Step 4: Click on "OK"

Resultant Data:

Let me know if you require pbix file for this small demo.

Give a Thumbs Up if this post helped you in any way and Mark This Post as Solution if it solved your query !!! Proud To Be a Super User !!! LinkedIn

[amitchandak]

@Anonymous , from power query you need pass this date a parameter

Date.AddDays(Date.StartOfMonth(Date.From(DateTime.LocalNow())),-1)

refer

https://www.biinsight.com/power-bi-desktop-query-parameters-part-1/