Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
@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.
Proud to be a Super User!
Paul on Linkedin.
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:
I think for my needs I don't require a calendar functionality but I will play around with both methods.
Thanks!
@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.
Proud to be a Super User!
Paul on Linkedin.
@Anonymous ,
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/
Does my Data Table require a calendar function or is it enough that I create a Calaendar table and link it to my data table to that tthe Calendar Fiscal Month will understand my Accounting Period and my Calendar FY will understand my Data Fiscal Year End?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
147 | |
107 | |
105 | |
87 | |
61 |