Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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

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!

 

Need Help
  • dax's avatar
    dax
    7 years ago

    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.

     

4 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    7 years ago

    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

  • dax's avatar
    dax
    Icon for Community Support rankCommunity Support
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        dax
        Icon for Community Support rankCommunity Support
        7 years ago

        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.