Forum Discussion
Do I need a Calendar?
Hello, I am quite confused with the need for a calendar.
My Data from SAP comes out like this, including multiple years of course and the only Analysis I am performing is Same Year Period vs Period and Different Years same period: As you can see my data has no calendar (day month/year) for each transaction. Do I need a Calendar? If so how will my seperate table calendar understand my Fiscal Year Ending and my Accounting Period unless I somwhow add a fiscal calendar in my data?
Accounting Period, Fiscal YearEnding Amount
1 2019 $1,000,000
2 2019 $1,300,000
3 2019 $1,100,000
4 2019 $1,500,000
5 2019 $1,400,000
6 2019 $1,600,000
7 2019 $1,700,000
8 2019 $1,900,000
9 2019 $1,800,000
10 2019 $1,3000,000
11 2019 $1,800,000
12 2019 $1,100,000
Anonymous
You can resort to using a calendar table, but in my opiniion it would be overkill.
In this instace I would create a "Period" table including your fiscal period and year columns, a newly created YearPeriod column (in both the period table and the data table) which is basically:YearPeriod = table[year] * 100 + table[period]
and also include an index column in the period table.
the period table should have unique values for the YearPeriod column, and you create a one-to-many relationship between the period table and your data table by linking the YearPeriod column.
Time Intelligence won't work, but you can write the equivalent DAX with the period table as your filter table (the index column will make it easy to reference filter periods).If you however want to use a calendar table, you will need to include a date field in your data table (for example, the first day of the month for each period) to be able to link to the calendar table via a relationship.
4 Replies
PaulDBrownCommunity Champion
Anonymous
You can resort to using a calendar table, but in my opiniion it would be overkill.
In this instace I would create a "Period" table including your fiscal period and year columns, a newly created YearPeriod column (in both the period table and the data table) which is basically:YearPeriod = table[year] * 100 + table[period]
and also include an index column in the period table.
the period table should have unique values for the YearPeriod column, and you create a one-to-many relationship between the period table and your data table by linking the YearPeriod column.
Time Intelligence won't work, but you can write the equivalent DAX with the period table as your filter table (the index column will make it easy to reference filter periods).If you however want to use a calendar table, you will need to include a date field in your data table (for example, the first day of the month for each period) to be able to link to the calendar table via a relationship.
camargos88Anonymous ,
If you need time intelligence functions, you need it. Also you can create a customizade calendar with your fiscal year dates.
https://powerobjects.com/2018/12/19/sorting-data-fiscal-year-power-bi/
Thanks I understand. Just for the fun of it was able to add a Date column to my Data but I had add many other columns to make it happen, some of which I deleted afterwards:
- First I created a conditional column to convert my accounting period to month ( Period 1 = month April) .
- Then I had to create another conditional column for calendar year (If month is 1,2,3 then Fiscal Year is 2019 else 2018).
- Finally I had to merge these two new columns as Date which appears like this (2019-04-01).
I think for my needs I don't require a calendar functionality but I will play around with both methods.
Thanks!