Skip to main content

Notifications

Power Apps - Building Power Apps
Unanswered

Extracting individual cells from Excel to get a total amount so far this tax year, dependent on day

(1) ShareShare
ReportReport
Posted on by 57
Hi all,
 
I've been using a similar format to the example data/table setup below and I want to be able to have a text item in Power Apps that shows the Total 'Taxable Income' so far this Tax Year (UK: 6 April 2024 to 5 April 2025), based off the current date when it's being viewed.
 
So if I went into the app today (16th Jan 2025), it would have added up all of the Taxable Income amounts (for example) for every month from 6th April 2024 through to the end of December. (9 payments)
 
Then in February, after the next payment amount, it would add the end of January's payment on..................and so on...........so that it adapts to the newly added information each time.
 
 
I thought about using LookUp(Table1, Col1="CY April Taxable Income", 'Col3') in an 'If' statement with Now() or Today() or something similar, somehow, but can't seem to get the right combination that works for the scenario. (Struggling with a migraine, so on limited energy as it is!)
 
 
I managed to get the Total Taxable Income for the whole tax year (or any value, if you swap out the "CY _____ Taxable Income" sections), using the following:
 
 
Text(Value
(Sum(
    (LookUp(Table1,Col1="CY April Taxable Income",'Col3')),
    (LookUp(Table1,Col1="CY May Taxable Income",'Col3')),
    (LookUp(Table1,Col1="CY June Taxable Income",'Col3')),
    (LookUp(Table1,Col1="CY July Taxable Income",'Col3')),
    (LookUp(Table1,Col1="CY August Taxable Income",'Col3')),
    (LookUp(Table1,Col1="CY September Taxable Income",'Col3')),
    (LookUp(Table1,Col1="CY October Taxable Income",'Col3')),
    (LookUp(Table1,Col1="CY November Taxable Income",'Col3')),
    (LookUp(Table1,Col1="CY December Taxable Income",'Col3')),
    (LookUp(Table1,Col1="CY January Taxable Income",'Col3')),
    (LookUp(Table1,Col1="CY February Taxable Income",'Col3')),
    (LookUp(Table1,Col1="CY March Taxable Income",'Col3')))),
    "£#,##0.00"
    )
 
Just wondering if there's a way of amending/adapting this with some form of 'Year To Date' formula, or if there's an even easier way.........?
 
Thanks.
 
 
 
 
Here's the fake data set I made up in Excel to try it out................
 
(CY is just shorthand for 'current year')
 
April    
CY April Taxable Income   £1,347.33
CY April Income Tax   £59.80
CY April Tax Code   1257L
CY April National Insurance   £29.14
CY April Payment Date   30 April 2024
May    
CY May Taxable Income   £1,347.33
CY May Income Tax   £59.80
CY May Tax Code   1257L
CY May National Insurance   £29.14
CY May Payment Date   31 May 2024
June    
CY June Taxable Income   £1,347.33
CY June Income Tax   £59.80
CY June Tax Code   1257L
CY June National Insurance   £29.14
CY June Payment Date   28 June 2024
July    
CY July Taxable Income   £1,347.33
CY July Income Tax   £59.80
CY July Tax Code   1257L
CY July National Insurance   £29.14
CY July Payment Date   31 July 2024
August    
CY August Taxable Income   £1,347.33
CY August Income Tax   £59.80
CY August Tax Code   1257L
CY August National Insurance   £29.14
CY August Payment Date   30 August 2024
September    
CY September Taxable Income   £1,347.33
CY September Income Tax   £59.80
CY September Tax Code   1257L
CY September National Insurance   £29.14
CY September Payment Date   30 September 2024
October    
CY October Taxable Income   £1,347.33
CY October Income Tax   £59.80
CY October Tax Code   1257L
CY October National Insurance   £29.14
CY October Payment Date   31 October 2024
November    
CY November Taxable Income   £1,347.33
CY November Income Tax   £59.80
CY November Tax Code   1257L
CY November National Insurance   £29.14
CY November Payment Date   29 November 2024
December    
CY December Taxable Income   £1,347.33
CY December Income Tax   £59.80
CY December Tax Code   1257L
CY December National Insurance   £29.14
CY December Payment Date   31 December 2024
January    
CY January Taxable Income   £1,347.33
CY January Income Tax   £59.80
CY January Tax Code   1257L
CY January National Insurance   £29.14
CY January Payment Date   31 January 2025
February    
CY February Taxable Income   £1,347.33
CY February Income Tax   £59.80
CY February Tax Code   1257L
CY February National Insurance   £29.14
CY February Payment Date   28 February 2025
March    
CY March Taxable Income   £1,347.33
CY March Income Tax   £59.80
CY March Tax Code   1257L
CY March National Insurance   £29.14
CY March Payment Date   31 March 2025
CY Total Taxable Income   £16,167.96
CY Total Tax Paid   £717.60
CY Total National Insurance Paid   £320.54
CY Total Pay After Tax   £15,129.82
 
 
Or image version/screen grab, if that's better...........
 
  • WarrenBelz Profile Picture
    WarrenBelz 145,580 on at
    Extracting individual cells from Excel to get a total amount so far this tax year, dependent on day
    A quick follow-up to see if you received the answer you were looking for or if you need further assistance.

    Please click Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it a Like.
    MVP (Business Applications)    Visit my blog Practical Power Apps    LinkedIn    Buy me a coffee
  • WarrenBelz Profile Picture
    WarrenBelz 145,580 on at
    Extracting individual cells from Excel to get a total amount so far this tax year, dependent on day
    A quick follow-up to see if you received the answer you were looking for or if you need further assistance.

    Please click Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it a Like.
    MVP (Business Applications)    Visit my blog Practical Power Apps    LinkedIn    Buy me a coffee
  • WarrenBelz Profile Picture
    WarrenBelz 145,580 on at
    Extracting individual cells from Excel to get a total amount so far this tax year, dependent on day
    If you put in a column for the month number (1 for April to 12 for March etc) and other columns for FY and Type then
    With(
       {
          _Month:
          If(
             Month(Today()) < 4,
             Month(Today()) + 9,
             Month(Today()) - 3
          )
       },
       Sum(
          Filter(
             Table1,
             MonthColumn <= _Month &&
             TypeColumn = Type &&
             FYColumn = FY
          )
       )
    ) 

    Please click Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it a Like.
    MVP (Business Applications)    Visit my blog Practical Power Apps    LinkedIn    Buy me a coffee
    ​​​​​​​
     
  • CU09091727-0 Profile Picture
    CU09091727-0 57 on at
    Extracting individual cells from Excel to get a total amount so far this tax year, dependent on day
     
    This isn't for Excel, though. It's for Power Apps.
     
    I'll be moving everything over to Dataverse eventually, but will still need the formulas for the calculations whether the data is coming from Excel or Dataverse.
     
    Concatenating doesn't solve the issue. I've already implemented that as part of calculations.
     
    I need a way of stipulating where today's date sits within the year, then working out from that.
     
    This doesn't seem to work, even when I add in the full coding for each month's cell, but is kind of what I need to get to:       (dates shown as dd/mm)
     
    If(   (06/04/YYYY<=(Today())<=05/05/YYYY), (Sum(April)),
          (06/05/YYYY<=(Today())<=05/06/YYYY), (Sum(April+May)),
          (06/06/YYYY<=(Today())<=05/07/YYYY), (Sum(April+May+June)),
          (06/07/YYYY<=(Today())<=05/08/YYYY), (Sum(April+May+June+July)),
          (06/08/YYYY<=(Today())<=05/09/YYYY), (Sum(April+May+June+July+August)),
          (06/09/YYYY<=(Today())<=05/10/YYYY), (Sum(April+May+June+July+August+September)),
          (06/10/YYYY<=(Today())<=05/11/YYYY), (Sum(April+May+June+July+August+September+October)),
          (06/11/YYYY<=(Today())<=05/12/YYYY), (Sum(April+May+June+July+August+September+October+November)),
          (06/12/YYYY<=(Today())<=05/01/YYYY+1), (Sum(April+May+June+July+August+September+October+November+December)),
    ..........etc
  • WarrenBelz Profile Picture
    WarrenBelz 145,580 on at
    Extracting individual cells from Excel to get a total amount so far this tax year, dependent on day
    Hi @CU09091727-0​​​​​​​
    You would then need another column for the year. The issue is that your data structure really does not lend itself to the type of query you need to perform without a large amount of hard coding the query values (as per your posted example). The other issue I alluded to is that Excel does not handle complex queries very well at times, especially as the data set get larger. Your other idea of concatenating the criteria for the query has some merit, but is really not the best way of handling queries.
  • CU09091727-0 Profile Picture
    CU09091727-0 57 on at
    Extracting individual cells from Excel to get a total amount so far this tax year, dependent on day
     
    The only issue with that is that because it relates to pay and tax.............I have multiple years in records. So I would need to be able to differentiate between which cells from which year I'm collating.
     
    So I need a formula for:
     
     
    (CY April Taxable Income) + (CY May Taxable Income) + (CY JuneTaxable Income)........up to the current date.
     
    It also needs to account for Tax Years that span from April to April, so the year might be different or the same as the current date, depending on when it's being viewed.
     
    I can easily adjust for CY-1 (previous year), CY-2........etc. once I have a finalised formula, but the 6th April - 5th April span is causing issues as I'm not sure how to account for it with part of the Tax Year being in a different year to the rest.
     
    Possibly some form of 'if' statement along the lines of:
     
    If today's date is between 6th (June or Tax Month 3) (Year A) and 5th (July or End of Tax Month 3) (Year A) then display (LookUps for Tax Months 1+2+3)
    .......etc.
     
    Or 
     
    Sum of all the entries in Col1 that contain 'CY' and 'Taxable Income'
     
     
    ?
  • WarrenBelz Profile Picture
    WarrenBelz 145,580 on at
    Extracting individual cells from Excel to get a total amount so far this tax year, dependent on day
    Apart from Excel being the "data source of last resort", which will cause you issues going forward, particularly as the data set grows, I think you need another column for the month instead of those headings. So Column1 would be Type - CY Taxable Income, Column2 would be Month - December and Column3 the Amount. So the formula would be
    Text(
       Sum(
          Filter(
             Table1,
             Col1 = "CY Taxable Income"
          ),
          'Col3'
       ),    
       "£#,##0.00"
    )
     
    Please click Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it a Like.
    MVP (Business Applications)    Visit my blog Practical Power Apps    LinkedIn    Buy me a coffee
  • CU09091727-0 Profile Picture
    CU09091727-0 57 on at
    Extracting individual cells from Excel to get a total amount so far this tax year, dependent on day
    Just thought of a better way to condense the existing query/formula a little more:
     
    LookUp(Table1,Col1=(Text("CY "& (Text(Month(Today()),"mmmm")) & " Taxable Income")),'Col3')
     
     
    ......but that still doesn't account for the 'Total to date from the beginning of the tax year' issue.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,580

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,909

Leaderboard