Combine Year and Accounting Period from two separate columns into a new column

[Anonymous]

Hello,

The data that I export from my financial systems has no date per se, only a year and period number according to our fiscal year. For example 2020-P1 = April 2020 etc. The data looks like this:

I require to know how many  working days are in each fiscal Year/ Period require to compare the data from one period to another so I figured that my first step was to create a calendar table which I did which also calculates the working days and Fiscal Month and Fiscal Quarter. Since my data is presented in Year / Period, how do I go about creating a Column in my Calendar which also reflects Year / Period ? Do I even need to do this in order to create the relationship between the Calendar and my Data? Here is my Calendar thus far:

[amitchandak · Accepted solution]

@Anonymous , in date table you can create like

Date = [year]&"-P" & [FY Month]

in Table

new date =
var _pos = search("P",[Year Period],,0)
return
format(date(left([Year Period],4), right([Year Period],len() -_pos),1),"MMM-YYYY")

[Anonymous]

Hello Date = [year]&"-P" & [FY Month] worked Great!

However I don't understand the purpose of new date =
var _pos = search("P",[Year Period],,0)
return
format(date(left([Year Period],4), right([Year Period],len() -_pos),1),"MMM-YYYY")

I tried to entere it in a new column to gain understanding but I get an error