Extract year, month and quarter from date column with data type as date.

[SanketBhagwat]

Hi.

When we extract a year,month or a quarter from a date column to create a new column, its data type is stored as either "Whole Number" or "text".
If we use Year=YEAR(datecolumn), it is stored as Whole Number and
if we use Year=FORMAT(datecolumn,"yyyy"), then it is stored as text.

And if we convert it to 'date' format, then the whole column value changes.

But it is common sense that the extracted column should be in date format only.

Does anyone know a way to achieve it?

Regards,

Sanket Bhhagwat

[d_gosbell]

---

@SanketBhagwat wrote:

But it is common sense that the extracted column should be in date format only.

---

I disagree. If we take your example and we extract the year from todays date it make much more sense to store this at the number 2021 and then change the summarization option to "Don't Summarize".

You cannot store just a year in a datetime column you have to include some value for the month and day and then you are wasting storeage on values you do not need.

[SanketBhagwat]

Agreed @d_gosbell .
But suppose if we want to use that column in a function which specifically requires a date column.
What to do then?

I posted this blog so as to make sure whether there is a way or not.
Even if there is no way, I can still implement my DAX.

Suppose if I want LastYearSum, then instead of SAMEPERIODLASTYEAR, I can use DATESINPERIOD and for that I would not need a separate YEAR column.

Anyways, thanks for your reply.
Appreciate your response.

Regards,
Sanket Bhagwat

[amitchandak]

@SanketBhagwat , how does year as date helps?

We can use year start or the year or end date if we need 

like startofyear([Date])

or date(Year([Date],1,1)

Start of month  = eomonth([Date],-1) +1

you have startofmonth,startofquarter also.

See if these can help to achieve what you want.

[SanketBhagwat]

Suppose if I am using SAMEPERIODLASTYEAR function and if i extract date using ;
Year=FORMAT(Datecolumn,"yyyy")

LastYear=CALCULATE(SUM(Sales),SAMEPERIODLASTYEAR(Year)),

then I will get an error as 'the column referenced to SAMEPERIODLASTYEAR is not a date column'.

[d_gosbell · Accepted solution]

---

@SanketBhagwat wrote:

Suppose if I am using SAMEPERIODLASTYEAR function and if i extract date using ;
Year=FORMAT(Datecolumn,"yyyy")

LastYear=CALCULATE(SUM(Sales),SAMEPERIODLASTYEAR(Year)),

then I will get an error as 'the column referenced to SAMEPERIODLASTYEAR is not a date column'.

---

Then you would use the original dateColumn that you reference in the expression for your year column. You only need one datetime column in your date table. All the other columns can be strings and numbers.

And if you are using any of the time intelligence functions like SAMEPERIODSLASTYEAR you really need to be using a dedicated date table. You should not be using a datetime column from a fact table or you risk getting incorrect results.

[SanketBhagwat]

Okay.

Thanks for the explanation @d_gosbell @amitchandak .