web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

Welcome to the Power Platform Communities
News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Apps / How to start numbering...
Power Apps
Answered

How to start numbering from one at the beginning of each month

(0) ShareShare
ReportReport
Posted on by RR-27101917-0 16

Hello everyone,

 

I am creating an application that needs to assign a unique number to each invoice. The unique number should consist of the serial number, the date of that month, and the year. For example, for the first invoice in this month it would be 01-05/2024 (serial number-month/year), and the next invoice in this month would be 02-05/2024, and so on. The first invoice from the following month would have a unique label of 01-06/2024. When the user selects the date of the next month, the sequence number 01 should appear in the field. The application has a date column, based on which the data could be calculated.

 

I have tried to create a calculated column in a SharePoint list, but I have not been able to come up with a solution. All data from the application is placed in a SharePoint sheet as well.

 

If you have a better suggestion, please share it here.

Categories:
Building Power Apps
I have the same question (0)
  • Verified answer
    WarrenBelz Profile Picture
    WarrenBelz 156,275 Most Valuable Professional on at

    Hi @Rad24 ,

    This works on a Test list here - I would suggest however that you change your invoice number to at least 3 characters (to allow for 1000 invoices)  or four (to allow for 10000), This will change a couple of the settings

    With(
 {
 _Data:
 Sort(
 DataSource,
 ID,
 SortOrder.Descending
 )
 },
 With(
 {
 _Last:
 AddColumns(
 _Data,
 SMonth,
 Value(
 Mid(
 YourSerialField,
 4, 
 2
 )
 ),
 SYear,
 Value(
 Right(
 YourSerialField,
 4
 )
 ),
 Serial,
 Value(
 Left(
 YourSerialField,
 2
 )
 )
 )
 },
 If(
 !IsBlank(
 LookUp(
 _Last,
 SMonth = Month(Today()) && SYear = Year(Today())
 ).YourSerialField
 ),
 Text(
 First(_Last).Serial + 1, 
 "00"
 ),
 "01" 
 ) & "-" & 
 Text(
 Month(Today()), 
 "00"
 ) & "/" & 
 Text(
 Year(Today())
 )
 )
)

    The total structure is not Delegable, so I have grabbed the latest records at the top.

     

    Please click Accept as solution 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 Thumbs Up.

    MVP (Business Applications)   Visit my blog Practical Power Apps

  • RR-27101917-0 Profile Picture
    RR-27101917-0 16 on at

    Hi @WarrenBelz,

    Thank you for your quick response.

    When I apply this formula, and click on yesterday's date, the application increases the day from the date by one.

    Rad24_0-1715851717288.png

    For yesterday's date, it gives me 16-05/2024 as a unique value, and it should be 01-05/2024, because it is the first invoice of this month. If I receive another invoice in a few days, it should be entered as 02-05/2024, because it is the second invoice in this month. When the invoice arrives in the next month, and user selects date when invoice arrived, its unique value should be 01-06/2024, because it is the first invoice in that month.

    Thanks for trying to help!

     

     

  • WarrenBelz Profile Picture
    WarrenBelz 156,275 Most Valuable Professional on at

    @Rad24 ,

    There is something wrong here - you must have other invoices up to 15 in May (15-05/2024) to get that result. If you have a look at the formulas at the bottom, it tests for matching Month/Year combinations and if one is found, adds one (I assume the latest ID will get the latest record). If nothing exists for the month, "01" is produced. I have tested it here on a model and it works as expected.

  • RR-27101917-0 Profile Picture
    RR-27101917-0 16 on at

    @WarrenBelz,

    I entered a couple of invoices, for testing purposes, but I didn't enter fifteen. Can I ask you to send a screenshot of your test SharePoint list, so I can check something?

    I don't have a column created that will assign a serial number (auto increment) for a new item in the SharePoint list, i.e. a new invoice. Does the "ID" column in your code refer to a manually created column, or a column that is automatically generated when the SharePoint list is created?

  • WarrenBelz Profile Picture
    WarrenBelz 156,275 Most Valuable Professional on at

    @Rad24 ,

    ID refers to the SharePoint ID column - you can also use Created if you want, you just need the newest records on top so the Lookup finds the newest matching record if present. The code I used is exactly what I posted and I manually entered either a matching May entry, where I got the  next number, or no May entries, where I generated 01.

  • RR-27101917-0 Profile Picture
    RR-27101917-0 16 on at

    @WarrenBelz,

    Everything works great! The Today() function confused me a bit, because the user should choose a specific date himself, and a unique value should be created based on that. I replaced it with a date column that the user will use when creating the unique value, and it works great. The code is attached, in case it helps someone.

    With(
 {
 _Data:
 Sort(
 DataSource,
 ID,
 SortOrder.Descending
 )
 },
 With(
 {
 _Last:
 AddColumns(
 _Data,
 SMonth,
 Value(
 Mid(
 SharepointColumnForUniqueValue,
 4, 
 2
 )
 ),
 SYear,
 Value(
 Right(
 SharepointColumnForUniqueValue,
 4
 )
 ),
 Serial,
 Value(
 Left(
 SharepointColumnForUniqueValue,
 2
 )
 )
 )
 },
 If(
 !IsBlank(
 LookUp(
 _Last,
 SMonth = Month(DateColumn.SelectedDate) && SYear = Year(DateColumn.SelectedDate)
 ).SharepointColumnForUniqueValue
 ),
 Text(
 First(_Last).Serial + 1, 
 "00"
 ),
 "01" 
 ) & "-" & 
 Text(
 Month(DateColumn.SelectedDate), 
 "00"
 ) & "/" & 
 Text(
 Year(DateColumn.SelectedDate)
 )
 )
)

    Thank you for your time!

  • WarrenBelz Profile Picture
    WarrenBelz 156,275 Most Valuable Professional on at

    @Rad24 ,

    Glad you got it working

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

News and Announcements

Welcome to the Power Platform Communities

Quick Links

Season of Sharing Community Challenge Winners!

Congratulations to our community stars!

Kudos to our 2025 Community Spotlight Honorees

Expanding mentorship, skilling, and AI innovation

Congratulations to the July Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Apps

#1
WarrenBelz Profile Picture

WarrenBelz 421 Most Valuable Professional

#2
11manish Profile Picture

11manish 153 Super User 2026 Season 2

#3
MS.Ragavendar Profile Picture

MS.Ragavendar 116 Super User 2026 Season 2

Last 30 days Overall leaderboard

Featured topics

Auto-Populate field Power Pages
Gallery Tabs - Show or Hide Tabs based on Field Value
How to patch multiple records at once
Enable Copilot for end users in model-driven apps
Question for everyone : How are you using Create Text GPT in AI Builder?
Community content - sample components, blogs etc. - Link to this page (http://aka.ms/PCFdemos)
Welcome to the Power Apps forum!

Product updates

Microsoft Power Platform Community release plans