Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
Do I need a date table if all of my data only contains a month and a year, but not a date?
I'm new to Power BI and I understand that creating a date table is one of the first things you should do, but the data I'm working with is by month and year, (example: January 2020, February 2019, etc.) not by a particular date (example: 01/05/2021). My year range is 2013-2019.
My data is like this across different tables. I've shown examples of what my tables look like below. All of the data isn't real, but the column headings represent what my tables look like.
I'm working on a turnover/attrition dashboard for our HR department. The data has already been aggregated into this format in Excel so I don't even know if I would have access to individual dates for this.
Attrition Overview
Year | Month | Quarter | Total Attrition | Voluntary | Involuntary | Headcount | Attrition Rate |
2013 | August | 3 | 50 | 25 | 25 | 400 | 12.5% |
Reason for Leaving
Year | Number of Employees Who Left | Percentage | Reason |
2013 | 2 | 2% | Personal Reason |
2014 | 6 | 6% | Health Reasons |
Attrition by department
Year | Number of Employees Who Left in Department | Percentage | Headcount by Dept | Department |
2013 | 1 | 1% | 10 | Human Resources |
2014 | 10 | 10% | 30 | Sales |
Attrition by Tenure
Year | Number of Employees | Percentage | Tenure Category |
2013 | 20 | 20% | Less than 1 year |
1-3 years |
Solved! Go to Solution.
Hi @Anonymous ,
This article explains why you need to create a calendar and the requirements of creating a calendar.
If there is no date column for year, month, and day in your original data, you don't need to create a calendar.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
This article explains why you need to create a calendar and the requirements of creating a calendar.
If there is no date column for year, month, and day in your original data, you don't need to create a calendar.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
This does not address the actual question asked by the original poster. Followingg the link provided sends you to the wrong information that does not answer the question
Hey @Anonymous ,
always depends on what you want to do.
If you want to use any time intelligence functions then you need a date table. If you want to show some numbers by time, then you should use a date table.
If you just want to calculate the number of employees per year you don't necessarily need a date table. But as a best practice I would recommend creating a date table.
Thank you for your help.
I'm still a bit confused because I don't understand how I would show a relationship between my data and the date table in this particular instance. I understand that if my data had actual dates that included a day, month, and year I'd be able to drag them to the date table to show the relationship.
But how would that work if my data only contains years? For my one table that has months and years, how would that work?
For instance, I tried creating the date table using DAX but it didn't work because none of my data is in the date format. But I can't format any of it as a date if none of it has a particular day associated with it.
So I ended up manually creating a date table in Excel and importing that, but before I try to form a relationship with this table, I'd like to know how it would work. I don't understand how I would be able to form a relationship with this date table either if it is also in the date format (showing all dates between 1/1/2013 and 12/31/2019).
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
66 |