Answered

Converting Fiscal Month and Year to Actual Dates

Like (0) Share

Report

Posted on 8 Mar 2025 10:54:04 by danbert1973

I have 2 choice fields in dataverse for 'Fiscal Month' and 'Fiscal Year'. These are in text format.

For example, Fiscal Month has choices such as 01-April, 02-May etc. up to 12-March.

Fiscal Year choices are i.e. 24/25, 25/26.

This is within a table used for budget forecasting. Because the dates are stored in this format, I am unable to use any date filtering (i.e. date range, last month, next month etc.) so I need a way to convert this information into a readable date (dd/mm/yyyy)

The date output I want would show as the 1st day of the month, so for example: Fiscal Year: 24/25, Fiscal Month: 01-April = 01/04/2024, or Fiscal Year: 24/25, Fiscal Month: 11-February = 01/02/2025.

I have achieved this in the past via a flow (by extracting either the first 2 or last 2 characters of fiscal year), and will no doubt be able to do this again, but I just wondered if it is possible to build this logic into a single formula column within dataverse instead?

I assume this would need to utilise an accompanying static table within the environment such as the below:

Month Conversion Table

Fiscal Month	Actual Month	Year Period
01-April	4	1
02-May	5	1
03-June	6	1
04-July	7	1
05-August	8	1
06-September	9	1
07-October	10	1
08-November	11	1
09-December	12	1
10-January	1	2
11-February	2	2
12-March	3	2

Any assistance is greatly appreciated.

Categories:

Entities

Tables

Microsoft Dataverse

All responses (2)

Answers (1)

Verified answer

danbert1973 84 on 28 Mar 2025 at 16:01:04

Like (0)

Report

Converting Fiscal Month and Year to Actual Dates

@MS.Ragavendar

Thank you for you response, however it did not solve the issue in my case. First of all the Fiscal Year was being selected from a choice field, not a lookup to the conversion table (that was an afterthought). I would have to change too much structure to use the lookup.

Secondly, I'm not sure if what you have proposed would work within a dataverse calculated column (with a model driven app) anyway as I don't believe a formula column can be saved with a date format.

I achieved my goal in the end by using a flow and that has proved to work without issue.
Suggested answer

MS.Ragavendar 1,062 on 08 Mar 2025 at 14:13:15

Like (0)

Report
Converting Fiscal Month and Year to Actual Dates

PlHi,

You can create a formula column.

// Extract the first two digits of the fiscal year (i.e., "24" from "24/25")

FirstYear = Value(Left(ThisRecord.'Fiscal Year', 2));

// Lookup the actual month and year period from the conversion table

MonthData = LookUp('Month Conversion Table', 'Fiscal Month' = ThisRecord.'Fiscal Month');

// Calculate the correct calendar year

CalendarYear = FirstYear + MonthData.'Year Period' - 1;

// Format as date: "01/MM/YYYY"

Date(2000 + CalendarYear, MonthData.'Actual Month', 1)

Advantages of This Approach

No Power Automate Flow needed

Automatically updates whenever Fiscal Year/Month changes

Works directly in Dataverse, making it available in Power Apps, Power BI, etc.

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.

Community site session details

Converting Fiscal Month and Year to Actual Dates

Advantages of This Approach

Helpful resources

News and Announcements

Quick Links

🌸 Community Spring Festival 2025 Challenge 🌸

Markus Franz – Community Spotlight

Kudos to the March Top 10 Community Stars!

Leaderboard

Featured topics

Product updates

Community site session details

Converting Fiscal Month and Year to Actual Dates

Advantages of This Approach

Helpful resources

News and Announcements

Quick Links

🌸 Community Spring Festival 2025 Challenge 🌸

Markus Franz – Community Spotlight

Kudos to the March Top 10 Community Stars!

Subscribe to this forum!

Select categories

Leaderboard

Featured topics

Product updates