cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
2019
Helper I
Helper I

Filter As Per Last N Months, Current Month and Next N Months

I have a table where I want to filter information in power query as per a date column (Start Date) in the table when are meeting the following criteria:

 

  1. Last 2 Months &
  2. Current Month &
  3. Next 6 Months

I cannot use (group by) and load the maximum values (latest) because I do have future year values.

 

Please find attached excel sheet data source file (click here), you can check my draft PBI file (click here)

 

Capture.PNG

1 ACCEPTED SOLUTION

I was just thinking this might be better:

 

let
    _TwoMonthsAgo =  Date.StartOfMonth(Date.AddMonths(DateTime.FixedLocalNow(), -2)),
    _SixMonthsAhead = Date.EndOfMonth(Date.AddMonths(DateTime.FixedLocalNow(), 6)),
    _FilterDate = Table.SelectRows(YOURTABLE, each [Start Date] >= _TwoMonthsAgo and [Start Date] <= _SixMonthsAhead)
in
    _FilterDate

View solution in original post

7 REPLIES 7
Rocco_sprmnt21
Super User
Super User

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fV1bjx03jv4rizOv6QNdS1WPne62247j+JKN7QzmwTMIBov1zgbGehf2r98qkRI/Siy/Gc0jlS68fqTov/718uaa8nL54RKWm+dfPt34df/3vSvL5W8/7MQv17iEg3rz88evN34jYm7EsMT9L/6gfu7UjahXv8Rvx9B88/aPP4n6+MI71wb7uP59/1t0+4f/RfSn0W2NnPZf7nPjh1cee83eHzOXm/s//jGvytUl+3Rz++fnm5D3f7/6+Pk/eWj0Cy3ZIO4fXY6J/XLz5I+/3wSnvnosqZ4UbTd48zCWm5f//b/zmng3rlLrxPoslkr2dVVtcOElu2WlRR275UU14r7bdPy81BW3Q1z7vOlYZaRzWocT9nXkfk4m1W31oNTY/dgbNdc1rWrBud18Oc41y0ngvLEcI8N288s//mc+p1jn3XkCqX2vof7Fb3Q9Sd+sS8cffCYi307qE2+RFows0044xlIX/Pzjv/oBy5Lobs6YbQ0iAhM/hXj8PjiZeT+Kvp2UCq0Jv9upfi3MqV/+OW12WY9zSlU6pq+6uuJISxqZ2FU2ZeFwg7T7cFz6Rgsap43LMmxGK4pj3pO9xC3UjyJRJL1K875RoMp6Yz3gYFKvqSRiUtgq3txxgsHL0McXm3w2ORKrer4jNa4sdIdyGpa8659MxEOzDZu9LnU7G13NNG3hM+wfxZOIJZKWwHk34X5fT7irEFiR81F06cxKlW1jFaphSddQCohNGLXLSgzRFwQjXU5f9YJEznfO92xVjrMfp/UL2xxQh3gzlSGsjx4r2qqsVv71o4bOkVRaPXw/zhsiGSTU33Dj9FVUH/LVXGjieoSaes2p7oa2Oi4pbpVFq3WdVuR8YV753IjdKvDZFyKOJxiXRAuqF65t0a461kb8NKvC/d6+gVwMn+3GaLU1NNmbXR2ashy3QgLZDSgqPNL9i9I9whPkDwQvV6vsUbUMIZ2IK9kjnFmURHVw4NaBTdlJaYIznUVXIZ2hcD9k8nlNzYIWfY67l2KIXTsLZFWluuo5rooZ2+0+vqUrYBkYLMPzn0h6IsnW8e9f34T4Y9NOlZEPJd68vcd3a/Jt8F1cL+IejVPfpUz7rZKpqV+utx+aO2gNfnweeHA96XHwj7d18EYsO459VfeUre9en71Ny/lnn72oaz6jvo3fWfKzV5F8K2NN+4pfLeV07H4a96v/Lrls3yM/ru47p3X7jgzfyVG//96JPP3gz9d99TnckkBUx/s4u/vXPjTuCeulu7F1GuSeXPkysPc8+daevEIMM4Tf6TSi6BytuFeglov2RX2sLpjptbBXGBYJMtBpCVU1LLTXdbR91aaaUraPdOnSg5vxEIMrZyP3nbLTJ+eLQUT9ZqCQ6SC+c6lN6rfTSWkf0ZtHkD1o7MEfvwaWLGvWpdpwjg+OVf/6aiPN+PLh7Ss+Anvo44tMcWPV1hMrFF/lqqrNY+L7j//VWMzH7bYqAnNFmU+9M9Gz+7u89PVWDbPJgg/udJ13ebnto0jMm790L6iGiH2vB3FtV/Z5JrJXBqzpxBWsd838NS0osotZvzluplTV7Yv4kXBIpR6pT+ByIJF9TDEeQnz6Ibo27+eJugSOZur56ps5dDRZ2RORuPsxkwdV49Q68xs5ivrZLIpjP0KRNMfn1C8O5TsQBOHtEIsjUQ6dxzg10SkmuXUMj0PdwzH24NLJ53MBTkr7t93jSAoZ6SF7YYCibldjKrujuQBXaK/hGjYm1hsYFaFLhbUzxuyynwpQRBJ0vdlrJsNvylWDNvRmIHbegP+16t7nzcQTtupJFx3QaJdu6xZh0mh1KoqwxgNM9ZNsDUYaOV2s78eT3xhdsL5IEE2RTSqsZOFdVl9uOr3I8A0eXzcyzq+3dPRm4JyyE6s5nq5LkRipnsNwpakaC45gpyVTPLPJQezaJXZZJj4LxQ71CSQIQcE7sqFSnWbv1A2IpCcSV6WFm4+w0Jr5mGcdXei7XZsilbAWVmx1VbClXU3kysLd0CNxodBvE8WmF1VledN6ulGLdxcNI+wz7/fSZna05PrdeeZ8EZxs3u5GY7tWxJkJRttdQKT2HT3ekutKbv7k9TitCNRZUJCchTPUiqtnySgPXf1r34mRAQp0CfrFE5CcdWjR97psdANdJR7WTly80m3s1359QF7rOQIu+8Z1p+nHJySdyvAI9FoYE+g8BWb2C0NIIEPw3f0wGEru6had4brkVXGjjNxYJYjfCcdIcRQoVDjGp68rNbK9Gz2gANDTzFCJo7tuDJGhHl8k9rKrOh54ZiE8LCt3BNh8GRSRYpqK50RQCHpV4Da07QqRYVs8C3GRlkv3cwbicXVBbMTorKTq0UUPSJv4Oc8fYsOvu+UXXyXXP4Rs+nShRcgYtzdiIf/JAwynltRjuk/TkhaK2VZ1dRmMSCAr0SVPwwWULOnqWnyga1wQsFkGTe9JQQHHiOW/5sjBU0fyFTjCxmnD9E9HXVxxRETPS0x/xUFNiOnI33xrimSKfa+Z9EgS3QYGfv/Xt0tDEUanLHvAVKYUjGOcDi0eBKCnoc4Rs9WbOwHNN7bC4pL1m0mJVW23haimdwYGmG7KhfjmFQv3I0pHni0cBMR8kWw/ay59hPvEjkAPCc7kCD3xkhfXVTkVMfivlzOPztUzX9CTAbvCAQDKebcrBGh5UUxauWAOcqQ+EvwTWCIHlff4rCFpVZhHVZvCRcdnuKgKOqBxxs9Gl24v2sfX6pJ1XleXv796uOufBWBoHppXEKtJS1dfchzbVvX0tY+XbqEn/R+3r5cO2U8f3s1SXZdtO8jrXmVV6opKBsuyjWteI2igbbCx1dUPTvsqMjGrtj4xnOPzn/I5wnZ99tt26SjD5P662DXFoBMPRTF6ocDIgbxB8kRGaK0wKtQES8mOa0ayMxSqxApELojg9E+6FITDJ0Xrq93womJwKwy5g8eG0SQnw53wA2602t5NrRYCBE7+dP4GA+q2Nm1zxZFYo9ToMJqHmoCchFWmWJNiB/sYkm+QOWi1HsLW0/Wgh2FFfkugX8Z5Cc1L+FFR75UBogcwBeNUTq93ouKGhDjgqNx9WlvSQhI4ABI0kLa7L6oSoaLWDBWOsVuut7oqJ0MyvM0w/DmhAEdajhEPvNXOSpFkPCIVAVz28urlDGUkj68ycbB150+etmyUgQ0dyt+3i53S5LtqqY5E1DqrX16vihHmx+KUlcZa954dI0OoPvq910C0KNcSEKcI5nWU1kA5SBE5+GbHJ7orhplEXytIisIJGogQC6szIIrvuDEIWZX+kDtLVeCiqGaQmlSvtHlpg2uYqhcRvVRU4EhWLDaN0qX4RbiTNIg4Kkm3jGcnyJinPKsFsWRP19UFDfZPnqgHxAFcZ8pwo/OgUoPOkz4HzdBVoOcowQA0dxvCFTC9pgr9ugyZv4G5uIwlgJ+kwBOPoM3As0sV/JDt01spu2rmOjIUTE2YjKtgQgTUDA3BkmFBo5tfjw9jJcUIVZ1HZ3maR0lOYPCqa5shqKGSKTRdou7rMZmlQC3pjhg2SHYEDGmytL5yYKuJGqxTCowk4MQwlEN6PCcx8Mv4WbXgfHZzh5fD1hQGThUEi9LYEJrI2Ll+gxxnfXdiKDzj9bgdLBGgzSLHQGVIIK5AeYQcAzvOxqp2mfMkc51RMUUWKIRDXAT8NsrxFNPX8aFlHz/OaHQOnF9AnpDagsjYICqQtUvHOkgHrHchgxl17CE4HMaNc2TCBZAKpeuKoCqY5mqOwcfDB4ILVsHhfn2zO8SCoxZSIz0Ax7kffquj+dPz6IcPBItBpcZIb0qhQTqa/lulB6ngUeSl8izsG4KQQtiLSL3GS7nKo4+EGOTpr2kTHTeqx+LJoZLitf7NA9ziFJ8CRLszdv8ukfQakcZO/SVxIs+Mb+7ucgexZrdp4dqTLtsYnBYuakBAVI6iMGLRQyPNeetF0KQpdC1coNk9Bs2XhPDi9XS+olR8gTpjHLmW4RxxOzXPEU3Y8kB9egLw66g9W0VpgDWpAtlEERumzFKXXvaVrQQWlQ8OjgMkddxEHZycEMykWQ7jSPT7W9YMkl+YRK3UaG3mUID50mG1kdOoxrhBXGOFRYjkO1SVPCXVCI9O6vT7EVZ7uuJ6xXVYt2G5SKxhdkiqXrp7dDVyCjr6hFpIqqLEacVPrCNth4QLZ3U2uFthLpO3/TIKE8OGuWK5GKoXXVU1BKT4Qq8+MCwtBSLe2XbYLS04soI9Lk3WVClrdgT0YaGPxPe1ZjQ4dRZQ1QgVu0M1+x7/JJ53hiPyQmUsAGQIbWPOx7BBPI6FhRyFhha0W4fl+uzlv7+6vn//XoSnMvmb//jnv8E9VGdV5Zx7ILCyHTdLlB15fgjcgwvghhy4YlUuPbQ0+LP7HhXPcW9xAbTEgMYdai3fEh1DAbl6ygwvCpRuvFzIjQqSLwBd++SD5ySRaZM4Dl2UUkzCU+XjRQOBSgfVkzIF7FAkvGS84Y6sBc6hg/sGyETi+BcBMmH03M5ijkevYeFUthUehyoiWIWpWJJUdRSuAFeVUJgWAU5CT6CdSt9CPse3siFIj3QUrLrPZ+HAgnc3pmQS1QNgehYvZ+G3L1aaKJdNuG22AZTtjEpbi56nJHf1F8cltWphQ1l8YXBn9zVQskQJ1UBuUcwkERWW9k11bVSgACGGckQ4UuvhCTpHxXP1QhsKklMcea5dYO9fR2HCwnl1SDTHnqVYj3PZxPfRdQ1cByDRLIzc2Jvu06LbtLpvFwlOxomf/9TqGjpsdJQg33b6zwT4nZQiFTZbwv3oRNYjLipZ0MGRtaquBcpwhPj0PUGq0Xw8tHDxv7Ao5vlbXQNURHQiFXM59U0hEu6GNQCdtjHiIrKIAzN9sm9Tucobs1kbicQnLz2jqWaippZyBXDuMc5x9NijJ2UVD3KYI6gJ8ja9rtrUZhrx2e95u/RXDtN1r+O7odcgqGURKzcsaY9yfo/kdfVUPIZ0x9Trped4mjh2RtoaQtkL7WRD+9xvI+djMNzsPHxMTopAVB4Ebw9PI2PwGIzC6P0H8MaF6d4BPTC3GgVn+2Ucs+9RpVRL49oJYskKkRDkwDV1MXlKXxgSwregGmDh11ZooiErUYC1hs8uG+tj5DuJW6rcrlBmABflg7vldIfiPXhImsn69A8r1Jwew3nLHT3G+uk4oNSDJanvV0GmiWBjjGYhFb5wqgqiw+4bcM2qoklRH9fngm1Cy8XrNS2Xz3wFhjOzW2kqq9jMFXNpVwsBx5K/FCri194qTgnT5C7dAR7hs8Ib6upTBzjh8oOqzwdfJ23u60Vi7IHqC71kNKDwAxdlhMwAXo53js1oWpGnr3wez94tb5xnEHMMz7184lgZvWfxHgpJpoF4txeUQb/HFV7cLid+x+7s+K4Gp1fYces6fUpIt7cRJw91OWGAXCqLTQzUYqQGVFjteLyR4tJNuahQV+3mUwBqlIubPss4uum67VqRPRoTqAhhlVud3V8+fYNdjvLei3IdOovW+PHd21/Oj4Keqm8q/AN1u2iifuQOhaWDy81YtzLHyKIE0ybTqeb38aqmS0IEz9rHIroWwFlFCsWxYQEftvsd/PhnVdOKI9oLeD7NxqE4gG0HF7bUbGqAt+S68HP1A/crz2PlJ1RSLYYmNrYH+5hrAJRuE1abzz/7j9PFDvbKOxwria9MNepmAbRfUlvTJ0MkKaGznLBTWj5e2iPJ6av8XAf3Ki+WAjMMrlfUcFiGq1W5uOqghRP4IxJeXAzJatWqG4bIku/mmtKCX5Wwsb1JwGAVHqL5YUn6HXuLp4w0UekvKy0716qQuw+D/kAEZ2Gq5c4ctGOtpOSgIRwdklrwWGrSlVys7W1iKz2Eamx1vIWrXI3Iu6Vf7RqzQmnmIZXSJePhF7hVI1I4iHgOSl7TN7EMk7efCxSoTS8/IqsYk5kYFPZ4FpDOzAyFgnBAswXKyG/oK5QBF24wxIDVqLr0UdX6OEi6KuCkQqhkDT2QtK3pcOupD+npYrZdcTl8vfzQap9nzyg5jlotR6QE11jq8wyOPLmjahzzkdf14UMOokYMKoPkFjjlfPm/y+hhwu02pWk2uHGZX/bPdpRLylbTcS0E0iVlW0Jfb9rOd1O4GgXj4dBPiTsjWFz85GX9uY2vXh8fWlmpVQj77FYqNWbE+Nl9ptdhlkjva9ouJ5WUx7V+Z94ndy1KVk/Vb/tbxZ/TBqNHqV3YvNutiepf4smTrNiAOqtSIHCJFgL2QyeTQfR6vLLyUUhlCXTL+E6ajGr0g2I0AL9bJgz1RNMFlC8sJqTLTTiKZXSOGsMwaZiOFNPzXf1krbuBLdI3HQLqatMKDGZHAzPPUw+laMwsQDI5E+ajgaU9C4CgTSxhYfQaHlWi3vJQ5Tb6n5lK07M6JnlA7tmJNNoKtRCqdaIa35eG1qgBx3YXPsd5Tf2zD9zkway0r2RA3GaUemtq+rNgzQGtMNRyjh0kDjq5K0pqZWm/cCOHWR+8/PnV4+07B+1xxqUdVQ03pOI07NZxlXtufqHpsrb7yvKtNHaan5e+2umGh/uUge1H7LSZN3m+o1KXXA5giVPxHJB1nxOgVao/HIS/83UmkMNy/uipHqKAwvLUkyZ6e1JuWGPyFrUowlYMCjDLc5cz6BnGsTtQwRmqkZwvaizUZ/HRo9rtijUxyg7FW9ilpTrXQT8n6mdfrzlB2yR8Kk/gc+/QArSfKCoF4yRPxI5CNUa8QXixaZWnBWGMIeXxbE5htTDUsyPUu72prd4/Jq707TpFI8sx1f4JZ41Fdgmo7my0gY77F6bsd5PMUJM+aQmpE9ki84LptVNCKA/cMJIBr4b2CsjMlWGoKKHul0rl0VKJ6Vy48By5qtdiO35gi+oZyhiwFG5gKqps1c2ixE6tHOUYbtqXVvmvhERC7ZVDAgOS4xdU2QRDKR7GmmrdF4sKbYzU15deB2p1MqHODOsJikpAUjQ7ktHrBxtQ252WcOkpYeMtHtYqTW0DuHmYbmEnL2XWS4+zp92U76h3R/F7lJSMXlOmw7eLFxbmUGNJLS7i1w9G+7vt20VqXifJyHyOqEoEOKAKEUtnEgiSTc/bIZo8HlLvEAU9C/ukjh1VWKsEw2Hx03IktKTnWGof8shmGy4cz+/2KUXvJ22nXqreDHMujZ7OIlSqHiqcFkG37ONJe7ZEJZZZ3vrqQr3uLBhF0CFhy6LpQSn5yCuaABH0SCgIdLhDS7hyH0DpGqPMUhVY1Ui0QdXBXd88vH11aVC2UeW0snMOc4PtT6CGpydJDlC1WaDx1fosW554HDsb9qLGDHw8QfqZK81NmMoFrts+6dNIhf7mVqM/VQUco9iFtXnl2MdwIHcurSJgAyCucKzbrTK+7Kho/0kowOlEkyH2BbGxn3VE7fD04cOHS+8V2vhBEr65wWbSPQeb5WLfTt05b5cdujfrmeTSFmW66GHt3YrnvpyhYRymneMERbFbqsXFg9BN5USnrcCuuRUSmNbeU6p3EU9BQ9z50hM180t3eM8wR3GOWpcZ7RC5TVsoFizcXrictFkMvVbj68Ro2ZFQoNMj79paykmaYI4eb0ZFyuupfObQux+TlKE2pOOgYi4m2rnwtgmylWykcsasJHnod6ulgyXy17tfrt61HgbTvRO5vxoZvSMiC75iUfn10xn5rOMAUc+sFlHPnmQR9aw5HVHXU0736TtdEAhX5pqU2Tpwns60aK51voCeuPLOvr5KPys8CBtUbE9tegnoUDnSrvsoFc/PPyeTxK0Z1hOXjcoS7Ta9qUBt57hXqjZOckjYi3SLwzeByPVXVsfhL4wtRm9KbWsb3LW0dlOO9URbxbjChwsesexkRes6GrMcQaKHJim916PtaHvUtaMd9JQvRw0kbSI4owXezarzP/BJUF1+o1el2NpXAD4uMUPDK15rT25+nRXiQqlC7M+7iMr7S3skLOjCQN9EYAyyp0QwjhYUlaFm/LRkKf2lId86cKV5qV+efFankBmu74NVtTolXZOiio+XudzRkJk9AuViEtQdnV1Wbloxt5nmo2CPAN8oQ3ks2Szkfnh/gl2z1Unx1Jyo6azRR/NpUbqy34Iq8fZglkaIgN/xeKsB9jW3F8AGJzeMHBXtID61ukD31gZ68PwyzGwG7qF6aUZEEj/RshbNfcO87FcVvNXMITqwsqg7ZngPkw+ynam+VJ2lAJZk4nUv7FXPDS3A9NztB8QlQB8mYHvbuKhvvNGXYfHTD8pwsJgacvwwzojp7kAbWDrxDtQBdz61Z6Ach2JVEc7W0wr5vHNj4XIh2DzyU8O9UbV2oDlwXI9EaTbRSxA+zbeaWuoclevwntrq5H73hrZLJXzITCjYkdGyufEn0zlexXULsMKFTggbLurjRSvRPWziSgX+AUOWKGXiPyyYnlFapV1l0KZB+e/bNlrIxslvhNObZQ44uv2AUXo8mqx+UAZhEl5/I6attZYMFx19MNwVZIUawKDMOV6sXr/H2pMx6OQm8Sf/O0BsPS/nMORNa5sSHbIjj33P585SYhiaXWdzwtRQ9wy3+WzpL+5/1PT5SKRy7Ww7Lq0JuPF/LPBTOm8I13vUSFonZvUDegaDfKAn2EilGz/Yr+r6F3KhN9tH4ca83uDyN63VCT8/nKg+18dgRbm7MnYjENKASr5wXxL0lKdLhmcBeu72g9ZHd/YouD1H44LhRviRbVQHBh7BdpEU4aBHuZWQ4oL+wFAaio/fpHtotSjzYKJ7g8UUndQN8okic6L6lF5Fcj2nUxkNcgmSI+MKp/SNpPaMntjRQl2r6PnSAW2TDtnQmYsjvNQz/HDAAqehjjNToAdBS1Jqyp6XCiO5C+tE9fUvUfm0UH6BgNOE2WXyZ+d4j8/CzwY7I73X8p/Q46RRFJnahhgnvXvTrNiNhNuuXkuT2TGoaRBxQ/8HYlpXzr10qk76uOb7zPcgAUDI1mHz215L3JspY3PUNzz5CZ6Zs1+I/OJoLhHjINn4P308PobFIEspxd3rDDBGXWBvm3u9fWj/25Nch3RIffp7/cuqhjbi7e+Z02wztZbE39/+fJ2kDnLr3F86BMVIgL5wJfOcxqhURhcQegB4M3H3HzNPQV2ICxKh7DXAA6fRS/bcYAplXUrYuaHGnGZu3fs20zUgjBeVj+rqxfICMYfyyzetBlT+aW2dw/8cPaH3gAMErxi7AOPz87VTMr1zwftTZOxDqeikCcj3T0p0FD3Q+FM6QUhD1PG3/wc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, #"Start Date" = _t, Asset = _t]),
    #"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"Item", type text}, {"Start Date", type date}, {"Asset", type text}}, "en-US"),
    #"Ordinate righe" = Table.Sort(#"Modificato tipo",{{"Start Date", Order.Ascending}}),
    #"Raggruppate righe" = Table.Group(#"Ordinate righe", {"Start Date"}, {{"all", each _},{"quanti", each Table.RowCount(_)}},GroupKind.Local,(x,y)=>Date.Month(x[Start Date])-Date.Month(y[Start Date])),
    #"Aggiunta colonna personalizzata" = Table.AddColumn(#"Raggruppate righe", "lastNmonths", each LCN([Start Date],2,6))
in
    #"Aggiunta colonna personalizzata"

 

 

 

where LCN is:

 

let
    lastNCurrNextM=(d,n,m) =>
    let 
     curr=Date.From(Date.StartOfMonth( DateTime.LocalNow())),
     lastN=Date.AddMonths(curr,-n),
     nextM=Date.AddMonths(curr,+m+1),
     md=Date.StartOfMonth(d),
     res=if md < lastN then "too past" else
                if md <curr then "lastN" else 
                if md=curr then "current" else 
                if md <nextM then "nextm" else "too future"
in
    res
    in lastNCurrNextM
justinh
Advocate III
Advocate III

Try this (or something like it, I'm writing it this off the top of my head, so the syntax might be wrong.)

 

let

    _CurrentMonthStart = Date.StartOfMonth(DateTime.FixedLocalNow()),
    _CurrentMonthEnd = Date.EndOfMonth(DateTime.FixedLocalNow()),
    _TwoMonthsAgo = Date.AddMonths(_CurrentMonthStart, -2),
    _SixMonthsAhead = Date.AddMonths(_CurrentMonthEnd, 6),
    _FilterDate = Table.SelectRows(_YOURTABLE, each [Start Date] >= _TwoMonthsAgo and [Start Date] <= _SixMonthsAhead)
in
    _FilterDate
 

 

 

I was just thinking this might be better:

 

let
    _TwoMonthsAgo =  Date.StartOfMonth(Date.AddMonths(DateTime.FixedLocalNow(), -2)),
    _SixMonthsAhead = Date.EndOfMonth(Date.AddMonths(DateTime.FixedLocalNow(), 6)),
    _FilterDate = Table.SelectRows(YOURTABLE, each [Start Date] >= _TwoMonthsAgo and [Start Date] <= _SixMonthsAhead)
in
    _FilterDate

View solution in original post

@justinh 

I used your method and it succeeded 100%, Thank you so much and also to the others who provided their solutions.

I have made minor changes to the code and I used power query formatter to make it look like this:

 

#"FilterDate" = 
    let
      _Last2Months = Date.StartOfMonth(Date.AddMonths(DateTime.Date(DateTime.FixedLocalNow()), - 2)),
      _Next6Months = Date.EndOfMonth(Date.AddMonths(DateTime.Date(DateTime.FixedLocalNow()), 6))
    in
      Table.SelectRows(
        #"Replaced Value1",
        each [Start Date] >= _Last2Months and [Start Date] <= _Next6Months
      )

 

 

Awesome! I'm glad it worked for you!

CNENFRNL
Community Champion
Community Champion

Hi, @2019 , you might want to try adding a customed column in PQ,

#"Added Custom" =
    let
        Today = DateTime.Date(DateTime.LocalNow()),
        Days_Till_Next_6_Months = Duration.Days(Date.AddMonths(Today,6)-Today)
    in
        Table.AddColumn(
            #"Replaced Value1",
            "Criteria", each
                if Date.IsInCurrentMonth([Start Date]) then "Current Month" else if Date.IsInPreviousMonth([Start Date]) then "Last 2 Months" else let Diff = Duration.Days([Start Date]-Today) in if Diff>0 and Diff<=Days_Till_Next_6_Months then "Next 6 Months" else "Other"
)

Screenshot 2021-02-06 072439.png

 

Here is the pbix file for your reference.

Thank you @CNENFRNL  for providing assistant to accomplish this, I have tried your approach but I have found out the below:

 

1. Last 2 months:

For the last 2 months you are using this code Date.IsInPreviousMonth([Start Date]) will return back the last 1 month info instead of 2 month info,

For example, when I filter the Criteria column to last 2 months I only see Jan-2021 info and there are no info for Dec-2020

Is there away where I can filter as per the last N months (N is where I specify number of months).

 

2. Next 6 Months:

I have tried your approach but I have found out if I filter the Criteria column to Next 6 Months,  

For Aug-2021 it shows me only 4 rows instead of total 16 rows

Is there a way to accomplish that?

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.