Convert Fiscal Period to Date

[JustinDallas]

Hello Folks,

I have a some data that only has Period and Year data.  I also would like to use a Master Calendar, but I don't know how to handle the Period issue.  I'm thinking about using the first day of a Period and linking it to the Calendar on that value, but I'm not sure how to calculate the first day in the period.

How do you reconcile Period based Account Summaries and Master Calendars?

[Anonymous]

Can you provide more details?

[JustinDallas]

Given a table with these columns, how do you link it to a Master Calendar?

[Anonymous]

does any of ACTINDX or PERIODID are anyhow related to dates or months?? If not then you cannot attain master calendar with granularity less than year.

[chriscammers · Accepted solution]

The first thing you have to do is figure out what month is the start of your year.

In your load script you need to establish some reference to the beginning month so let's say your fiscal year begins in July

set a variable to the beginning month number.

let vYearBeginMonth = 7;

then load your periods, the monthstart function has an offset parameter that allows you to change the begin date that will be returned so as long as we know when the year starts then you can adjust the periods to reflect the right date.

Load

Year, Period, Monthstart(MakeDate(Year,Period,1),($(vYearBeginMonth) - 12) - 1) as PeriodStartDate

Inline [

Year, Period

2016,1

2016,2

2016,3

2016,4

2016,5

2016,6

2016,7

2016,8

2016,9

2016,10

2016,11

2016,12

];