cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

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

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:

Period.JPG

 

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:

 

Calendar.JPG

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
Super User IV

@ESDC , 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")



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

2 REPLIES 2
Super User IV
Super User IV

@ESDC , 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")



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

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 

 

error.JPG

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors