Designer - create every quarter for every investment record

[Pirasant]

Hi all, 

I am struggling with a problem. I want every quarter to be represented from the first investment date until today (2022 Q3). This is the data I have: 

Company name	Date	Round	Share price	Valuation
x	Q4 2021	1	1	1 000 000
x	Q1 2022	2	2	3 000 000
y	Q1 2022	1	4	1 000 000

The desired output: 

Company name	Date	Round	Share price	Valuation
x	Q4 2021	1	1	1 000 000
x	Q1 2022	2	2	3 000 000
x	Q2 2022	2	2	3 000 000
x	Q3 2022	2	2	3 000 000
y	Q1 2022	1	4	1 000 000
y	Q2 2022	1	4	1 000 000
y	Q3 2022	1	4	1 000 000

[JamesCharnley]

Hi @Pirasant,

Not sure if I am massively overcomplicating this, but since it's been a while and nobody else has answered I'm assuming there's nothing really easy I've missed. I've been playing around with this for a while and this is what I've come up with. It works for your output and could be dynamic but it depends mainly on how the rounds work, I can't quite work it out from the sample.

I've used generate rows alongside an enhanced DateTimeTrim macro by @DataNath  to get quarters(More info at this link: https://community.alteryx.com/t5/Public-Community-Gallery/Enhanced-DateTimeTrim/ta-p/972535)

Hopefully this helps a little or at least points you in the right direction

[DataNath · Accepted solution]

How does this look @Pirasant? Can't think of any massive factors that may trip this up but do let us know if there's any issues when applying this to a wider data set.

[Pirasant · Accepted solution]

Thank you very much! This worked like a charm