This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
Hello,
I've been scratching my head for the following problem:
I have two distinct columns in Excel. One is the fiscal year and another depicts the fiscal month (period). How to I convert these into a calendar date in MM/DD/YYYY format in Power BI (preferably) or Excel? E.g. FY 2023 | Period 7 | 10/31/2023
DD as the last day of the month. Fiscal year starts April 1st, no joke.
| Fiscal Year | Period |
|---|---|
| 2023 | 7 |
| 2023 | 8 |
| 2023 | 9 |
| 2023 | 10 |
| 2023 | 11 |
| 2023 | 12 |
| 2024 | 1 |
| 2024 | 2 |
| 2024 | 3 |
| 2024 | 4 |
| 2023 | 1 |
| 2023 | 2 |
| 2023 | 3 |
| 2023 | 4 |
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
For the first day of the calendar month:
=DATE(Fiscal Year, Period+3, 1)
For the last day of the calendar month:
=DATE(Fiscal Year, Period+4, 0)
The DAX formula is perfect and makes the data easier to update. Thank you so much! Edit: I'm realizing that the date for fiscal periods 10 through 12 aren't correct. The fiscal year may be 2023, but the calendar year should be +1. E.g. FY 2023 | Period 10 | Calendar Date 1/31/2024
BTW
Another formula solution
=EOMONTH(DATE(A2,IF(B2<10,B2+3,B2-9),1),0)
Regards
Jeovany
Hi Breanna
Here is a Power Query solution
Please, upload the table to PQ and add a Custom column with the formula
=Date.EndOfMonth(#date([Fiscal Year],if [Period] < 10 then [Period] +3 else [Period]-9,1))
As indicated in the picture below with the values you provided.
I hope this helps you and gives a solution to your problem
Do let me know if you need more help.
Regards
Jeovany
Thank you so much!