Forum Discussion
Convert YEAR QRT into a type Date
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
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.
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
2 Replies
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.
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