Answered

Submitting a 12 Fiscal Year items list.

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Hi guys, im new to powerapps. Ive been using it for about two months now. My boss has me developing a Tracker app where the users have a button to automatically create a twelve month item list based off a submitted Start Date. He requested a a Fiscal Year column in the table as well.

I got it to work relatively well by creating the items based of the Start Date using the DateAdd function. So example, using Collect the list is created in SP with:

DateAdd(Date, 1, Months)

DateAdd(Date, 2, Months)

and so on..

The problem is the Fiscal Year though. Since our FY is from October to September i am having a difficult time creating the logic to enter the correct FY for the items based on the submitted Start Date. Setting a logic like:

"If Month>=10, Fiscal Year+1, Fiscal Year", doesn't work because if the Start Date submitted is 9/1/2020 then a 12 item list would be created rolling over into Jan of 2021. January's value is 1 which is less than 10 and returning a FY of 2020.

Im probably over thinking this but if anyone could assist with the logic it'd be much appreciated.

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (1)

Verified answer

Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
You could create this table on your app's OnStart property and reference it when you need to do FY logic.

ClearCollect(FiscalYearSchedule, Table( {FiscalMonth:1,MonthName:"October",MonthNumber:10} ,{FiscalMonth:2,MonthName:"November",MonthNumber:11} ,{FiscalMonth:3,MonthName:"December",MonthNumber:12} ,{FiscalMonth:4,MonthName:"January",MonthNumber:1} ,{FiscalMonth:5,MonthName:"February",MonthNumber:2} ,{FiscalMonth:6,MonthName:"March",MonthNumber:3} ,{FiscalMonth:7,MonthName:"April",MonthNumber:4} ,{FiscalMonth:8,MonthName:"May",MonthNumber:5} ,{FiscalMonth:9,MonthName:"June",MonthNumber:6} ,{FiscalMonth:10,MonthName:"July",MonthNumber:7} ,{FiscalMonth:11,MonthName:"August",MonthNumber:8} ,{FiscalMonth:12,MonthName:"September",MonthNumber:9} ))

---
If this answered your question, please click "Accept Solution". If this helped, please Thumbs Up.

Was this reply helpful? Yes No
AGroegelich 140 on at

Like (0)

Report
Copy link

Link copied!

You have to cheat. I would add a second (hidden) column numbered 1 to 12. Name the column for example FiscMonthNr. In your case October will be month 1 and September month 12.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!

@Anonymous Thanks for this. Never though about using a collection since I've never used a collected table before. Your conversion table worked like a charm, although it look me a bit to rework my logic for it. Not only did it work, it optimized the variables and formulas i over complicated trying to figure this out. Thanks again!

Was this reply helpful? Yes No