Formula to populate previous number in lookup columns


Every month i make invoice with details

  1. first i make invoice number
  2. then i make details of the invoice
  3. then i make summary of the invice
  4. every period i make an invoice
  5. my calculation system is to make total work done till now and deduct Previous work done to get current value as shown in the doc
    in my doc ( Pay Details ) page
    • i select pay number

I added the formula for you!

  • Filter rows to only get rows where Pay No is less than itself
  • Sort this row by Pay No to get highest number first
  • Return the Pay No value of the first item in this list
[Pay Details].Filter(CurrentValue.[Pay No] < thisRow.[Pay No]).Sort(false, [Pay Details].[Pay No]).First().[Pay No]
1 Like

thank you very much
the formula works well
i need more help
when i select Previous number manully i have option to add filter to the lookup value as shown


when iam using formula it populate this no and the option to add filter isnot available
and it generate number from another project as shown

can you help me to select Previous number for the same project in pay no table as shown

i trial to modify the formula not working as shown