Group data by date range

%3CLINGO-SUB%20id%3D%22lingo-sub-1589853%22%20slang%3D%22en-US%22%3EGroup%20data%20by%20date%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1589853%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20power%20querry%20with%20a%20list%20of%20clients%20invocies%2C%20when%20they%20where%20created%20and%20when%20is%20the%20due%20date%20for%20the%20client%20to%20pay.%20I%20want%20to%20make%20a%20dynamic%20table%20or%20a%20slicer%20that%20groups%20the%20invoices%20in%20these%20ranges%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3Epayment%20has%20been%20delayed%20between%201%20to%205%20days%3C%2FLI%3E%3CLI%3Epayment%20has%20been%20delayed%20between%206%20to%2029%20days%3C%2FLI%3E%3CLI%3Epayment%20has%20been%20delayed%20by%2030%20days%20or%20more%3C%2FLI%3E%3C%2FUL%3E%3CP%3EI've%20attached%20an%20example%20excel.%3C%2FP%3E%3CP%3EI%20appreciate%20your%20help%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1589853%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1590199%22%20slang%3D%22en-US%22%3ERe%3A%20Group%20data%20by%20date%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1590199%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F760123%22%20target%3D%22_blank%22%3E%40AdrianRS87%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20a%20starter%20for%20you.%20I'll%20let%20you%20refine%20it%20further.%20On%20Sheet1%20there's%20a%20single%20cell%20A2%20into%20which%20you%20can%20enter%20a%20number%20of%20days.%20The%20following%20FILTER%20formula%2C%20then%2C%20in%20cell%20C2%2C%20will%20list%20all%20those%20that%20exceed%20that%20number%20of%20days.%3C%2FP%3E%3CP%3E%3DFILTER(Hoja1!A2%3AF27%2C(ISBLANK(Hoja1!G2%3AG27)*(TODAY()-Hoja1!F2%3AF27)%26gt%3BSheet1!A2))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20FILTER%20function%20is%20only%20available%20in%20the%20newest%20version%20of%20Excel%2C%20so%20if%20it%20doesn't%20work%20for%20you%2C%20you'll%20need%20to%20upgrade.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

I have a power querry with a list of clients invocies, when they where created and when is the due date for the client to pay. I want to make a dynamic table or a slicer that groups the invoices in these ranges:

 

  • payment has been delayed between 1 to 5 days
  • payment has been delayed between 6 to 29 days
  • payment has been delayed by 30 days or more

I've attached an example excel.

I appreciate your help

1 Reply
Highlighted

@AdrianRS87 

 

Here's a starter for you. I'll let you refine it further. On Sheet1 there's a single cell A2 into which you can enter a number of days. The following FILTER formula, then, in cell C2, will list all those that exceed that number of days.

=FILTER(Hoja1!A2:F27,(ISBLANK(Hoja1!G2:G27)*(TODAY()-Hoja1!F2:F27)>Sheet1!A2))

 

The FILTER function is only available in the newest version of Excel, so if it doesn't work for you, you'll need to upgrade.