Forum Discussion

Kalachuchi's avatar
Kalachuchi
Helper III
5 years ago
Solved

Filter row depending on year

Hello, I just want to make my filter dynamic on power query 

 

= Table.SelectRows(Source, each ([Product] = "MAC") and ([#"Year(based on client year end)"] = "2021"))

so instead of 2021,

I have created another table which holds the current year 

if Date.Month(DateTime.LocalNow())< 7 then Date.Year(DateTime.LocalNow()) else Date.Year(DateTime.LocalNow())+1

 

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • CNENFRNL's avatar
    CNENFRNL
    5 years ago

    Hi, Kalachuchi , based on your description, I think you can merge the calculation of current year into your selection this way,

     

    = Table.SelectRows(Source, each ([Product] = "MAC") and ([#"Year(based on client year end)"] = Text.From(if Date.Month(DateTime.LocalNow())< 7 then Date.Year(DateTime.LocalNow()) else Date.Year(DateTime.LocalNow())+1)))

     

    or more efficiently

     

    cy = Text.From(if Date.Month(DateTime.LocalNow())< 7 then Date.Year(DateTime.LocalNow()) else Date.Year(DateTime.LocalNow())+1),
#"Selected Rows" = Table.SelectRows(Source, each ([Product] = "MAC") and ([#"Year(based on client year end)"] = cy))

     

1 Reply

  • CNENFRNL's avatar
    CNENFRNL
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hi, Kalachuchi , based on your description, I think you can merge the calculation of current year into your selection this way,

     

    = Table.SelectRows(Source, each ([Product] = "MAC") and ([#"Year(based on client year end)"] = Text.From(if Date.Month(DateTime.LocalNow())< 7 then Date.Year(DateTime.LocalNow()) else Date.Year(DateTime.LocalNow())+1)))

     

    or more efficiently

     

    cy = Text.From(if Date.Month(DateTime.LocalNow())< 7 then Date.Year(DateTime.LocalNow()) else Date.Year(DateTime.LocalNow())+1),
#"Selected Rows" = Table.SelectRows(Source, each ([Product] = "MAC") and ([#"Year(based on client year end)"] = cy))