Date Table - Years and Months Only

[Anonymous]

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

[agelee]

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.

[Anonymous · Accepted 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.

[Anonymous]

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

[selimovd]

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 regards

Denis

Blog: WhatTheFact.bi

[Anonymous]

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).