Power Platform Community Forum Thread Details

I have been using formula that create a new 'Tracker ID' based on the following criteria:

If the current item already has a 'Tracker ID' value, that value is used.
If not, it generates a new 'Tracker ID' by combining the current month and year with a count of valid records in the 'Issue Tracker', incremented by 1.

If(ThisItem.'Tracker ID'<>Blank(),ThisItem.'Tracker ID',Month(Now())&"/"&Year(Now())&"/"&Sum(CountIf('Issue Tracker',ThisItem.ID<>0),1))

Now, I want to reset the sequence number to 1 when new months starts. Please guide.

Categories:

Building Power Apps

I am not sure what data source you are using, but if you are using a SharePoint List, you can take advantage of using the out of the box fields to know when your issue was first created, by using the out of the box SharePoint Created field in particular.

This can really help to know what sequence number to assign.

In order to know what sequence number to assign, you should get a count of the current number of issues you have between the first day of the current month, and the first day of the next month not inclusive of that day. To do so, you can try a formula like the below, you can just use it wherever you used the original formula and replace your original formula with the following instead:

If(
 ThisItem.'Tracker ID' <> Blank(),
 ThisItem.'Tracker ID',
 With(
 {wFirstDayOfMonth: Date(Year(Today()), Month(Today()), 1)},
 Month(Now()) & "/" & Year(Now()) & "/" & 
 CountRows(
 Filter(
 'Issue Tracker', 
 Created >= wFirstDayOfMonth &&
 Created < DateAdd(wFirstDayOfMonth, 1, TimeUnit.Months)
 )
 ) + 1
 )
)

See if it helps @sanjeevkumar1