Forum Discussion

Kalachuchi's avatar
Kalachuchi
Icon for Helper III rankHelper 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...
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))

     

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))