Convert YEAR QRT into a type Date

[Anonymous]

I have 2 YEAR QUARTER columns and I want to subtract them to find how many Qarters apart they are

format of each column is "2021 Q3"

PQ returns are  erro if I try to convert to Date.  whats the best way to manipulate in-column to create a Date type from this format?

thanks

[edhans · Accepted solution]

The following formula in a new column will convert that to the last day of the quarter.

Date.EndOfMonth(#date(Number.From(Text.Start([Quarter], 4)),Number.From(Text.End([Quarter], 1)) * 3, 1))

From there you can move it to the first day of the quarter using Date.AddMonths(the date, -3) and then use Date.StartOfMonth() to move that to the first day of the month.

[slorin · Accepted solution]

Hi,

4*(Number.From(Text.Start([YEAR QUARTER 2],4))
  -Number.From(Text.Start([YEAR QUARTER 1],4)))
+Number.From(Text.End([YEAR QUARTER 2],1))
-Number.From(Text.End([YEAR QUARTER 1],1))

2021 Q3 --> 2022 Q1 

4*(2022 -2021) + 1 - 3 = 2  Quarters

Stéphane

[edhans · Accepted solution]

The following formula in a new column will convert that to the last day of the quarter.

Date.EndOfMonth(#date(Number.From(Text.Start([Quarter], 4)),Number.From(Text.End([Quarter], 1)) * 3, 1))

From there you can move it to the first day of the quarter using Date.AddMonths(the date, -3) and then use Date.StartOfMonth() to move that to the first day of the month.