Forum Discussion
Date Table - Years and Months Only
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 |
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.
5 Replies
- selimovd
Most Valuable Professional
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.
If you need any help please let me know.If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍Best regardsDenisBlog: WhatTheFact.biThank 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).
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.
There is an option. Add to your main fact table month in one of the following format "YYYY-mm" or "YYYY-MM" then change the column data type to "Date". It should replace your month with first day of month. Then you can use this column to join to full Calendar table (with dates) but in fact it will join tables only on one date (first day of the month) and it will work with all time intelligence functions.
