M Query - Last Year This Month, Last Year Last Month

[Anonymous]

Hi - I'm trying to come up with a way to only bring in certain data based on a date column in the Query Editor.

I have a date column that has the first day of each month for last year (January 1st, 2018; February 1st, 2018, etc)

I'm trying to filter my query so that I only get two months worth of 'last year' data returned. I want "Last Year This Month" and "Last Year Last Month" data.

Example: Today is August 28, 2019. I want to bring back August 2018 and July 2018 data in my query editor layer.

Any help would be greatly appreciated!

[dax]

Hi CT767,

You could try below query to see whether it works or not

let
    
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc5RCgAhCEXRvfgdpDY6tZZo/9tInCBlfg9yn3MCI/VKlaAAIaxy5HXhC+ogF7qDRuB8YQ0xeWJE4sk4w3xBa8vwPUJpRvKOZThnxSuaTmJlWKX9l9GkwVob", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [date = _t, v1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}, {"v1", Int64.Type}}),
    
    #"FILTER"=Table.SelectRows(#"Changed Type",each ([date] >=#date(Date.Year(DateTime.LocalNow()) -1 , Date.Month(DateTime.LocalNow())-1, 1)) 
    and  ([date] <#date(Date.Year(DateTime.LocalNow()) -1 , Date.Month(DateTime.LocalNow())+1, 1)))
  in  
#"FILTER"

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @dax ,

I tried the below code, what you posted but changed the "AND" to "OR".

I'm getting an error "Expression.SyntaxError: Token Comma expected", I'm unable to find what comma i'm missing.

---

---

= Table.SelectRows(#"Removed Columns1", each ([Snapshot Date] >= #date(Date.Year(DateTime.LocalNow())-1, Date.Month(DateTime.LocalNow())-1,1)) OR ([Snapshot Date] <#date(Date.Year(DateTime.LocalNow())-1, Date.Month(DateTime.LocalNow())+1,1)))

[dax · Accepted solution]

Hi  CT767,

You might miss comma at the end of above row, I test my code in my enviornment, it will work well(no error, but result might not be what you want)

let
    
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc5RCgAhCEXRvfgdpDY6tZZo/9tInCBlfg9yn3MCI/VKlaAAIaxy5HXhC+ogF7qDRuB8YQ0xeWJE4sk4w3xBa8vwPUJpRvKOZThnxSuaTmJlWKX9l9GkwVob", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [date = _t, v1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}, {"v1", Int64.Type}}),
    
    #"FILTER"=Table.SelectRows(#"Changed Type",each ([date] >=#date(Date.Year(DateTime.LocalNow()) -1 , Date.Month(DateTime.LocalNow())-1, 1)) 
    or ([date] <#date(Date.Year(DateTime.LocalNow()) -1 , Date.Month(DateTime.LocalNow())+1, 1)))
  in  
#"FILTER"

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MFelix]

Hi @Anonymous ,

To what I can understand you want to get the data base on an dinamic filter based on current date make a filter on the query: and select any date you will get something similar to this:

= Table.SelectRows(#"Changed Type", each ([Date] = #date(2018, 8, 1) or [Date] = #date(2018, 12, 1)))

Then just change the date part of the two dates for the following formula:

= Table.SelectRows(#"Changed Type", each ([Date] = #date(Date.Year(DateTime.LocalNow()) -1 , Date.Month(DateTime.LocalNow()), 1) or [Date] = #date(Date.Year(DateTime.LocalNow()) -1, 12, 1)))

This should give expected result.

Regards,

MFelix