Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
2019
Helper II
Helper II

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

8 REPLIES 8
Anonymous
Not applicable

You can use the isinprevious and isinthenext date/tiem filter options when select the dropdown from the column you want to filter

 

example below

 

Table.SelectRows(FilterFiles, each Date.IsInPreviousNMonths([Date created], 2))

Anonymous
Not applicable

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 IV
Advocate IV

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

@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.


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors