Creating a constant value for each month

I put together some test data and was able to do what you want to do. My test data had an ID column, a date column and a value column, which is the constant that will be repeated after the first entry. Here's a screenshot of my ETL:

Also, you can copy and past this line into a blank ETL and it will have all the information in the tiles for you:

{"contentType":"domo/dataflow-actions","data":[{"name":"Test Constant Month 1","id":"fd5ef49d-5317-4abc-ab43-02a363f6bbb1","type":"LoadFromVault","gui":{"x":36,"y":180,"color":3238043,"colorSource":null},"dependsOn":[],"removeByDefault":false,"notes":[],"dataSourceId":"49941246-fc36-48c8-a5a6-077b9cff470c","executeFlowWhenUpdated":false,"onlyLoadNewVersions":false,"columnSettings":{},"visiblePartitionColumn":null,"versionWindow":null},{"name":"Add Constants","id":"050c89db-5621-4c79-ae4a-94093298cee1","type":"Constant","gui":{"x":144,"y":180,"color":null,"colorSource":null},"dependsOn":["fd5ef49d-5317-4abc-ab43-02a363f6bbb1"],"removeByDefault":false,"notes":[],"fields":[{"name":"JoinKey","type":"LONG","expr":null,"value":"1"}]},{"name":"Group By All Months","id":"974f2a27-c169-4e07-b7f1-ebdc2f1fcc3f","type":"GroupBy","gui":{"x":252,"y":84,"color":null,"colorSource":null},"dependsOn":["050c89db-5621-4c79-ae4a-94093298cee1"],"removeByDefault":false,"notes":[],"addLineNumber":false,"lineNumberFieldname":null,"giveBackRow":false,"allRows":false,"groups":[{"name":"Date"},{"name":"JoinKey"}],"partitionedAggregation":false,"fields":[{"name":"Count","source":"ID","type":"COUNT_ALL","valuefield":null}]},{"name":"Join Data","id":"42f6d24e-2a1d-4028-8866-5cf098274e07","type":"MergeJoin","gui":{"x":336,"y":168,"color":null,"colorSource":null},"dependsOn":["974f2a27-c169-4e07-b7f1-ebdc2f1fcc3f","050c89db-5621-4c79-ae4a-94093298cee1"],"removeByDefault":false,"notes":[],"joinType":"LEFT OUTER","step1":"974f2a27-c169-4e07-b7f1-ebdc2f1fcc3f","step2":"050c89db-5621-4c79-ae4a-94093298cee1","keys1":["JoinKey"],"keys2":["JoinKey"],"schemaModification1":[],"schemaModification2":[{"name":"Date","rename":"StartDate","remove":false},{"name":"JoinKey","rename":"","remove":true}],"partitioningInputId":""},{"name":"Add Formula","id":"6e74aab4-f11c-42f8-98f1-3ebf29ffe706","type":"ExpressionEvaluator","gui":{"x":456,"y":168,"color":null,"colorSource":null},"dependsOn":["42f6d24e-2a1d-4028-8866-5cf098274e07"],"removeByDefault":false,"notes":[],"expressions":[{"expression":"CASE when `Date` < `StartDate` then NULL ELSE `Value` END","fieldName":"Value","settings":null}]},{"name":"Test Constant Month","id":"b72a9ddb-2ff7-4fa4-a68a-dca58b5b997b","type":"PublishToVault","gui":{"x":588,"y":168,"color":null,"colorSource":null},"dependsOn":["6e74aab4-f11c-42f8-98f1-3ebf29ffe706"],"removeByDefault":false,"notes":[],"dataSource":{"guid":"ff298a29-fe48-4f57-95ca-92e1d211b3e6","type":"DataFlow","name":"Test Constant Month","cloudId":"domo"},"versionChainType":"REPLACE","partitionIdColumns":[]}]}

Here is a breakdown of what each tile is doing

Add Constants - add a column called JoinKey with a constant value of 1 that will be used for the joining

Group By All Months - Creating a list of all months in the dataset by adding the date field and the JoinKey column in the select list and then aggregate by any field (this field doesn't matter, we just need it to get our distinct list of months)

Join Data - Perform a left join with the group by on the left and join on JoinKey. On the right side, alter the date column to be renamed to StartDate as we will use it in the next tile. You can drop the JoinKey on the right side.

Add Formula - Overwrite your value field from your dataset by looking for when the date field is less than the StartDate field and make it null, otherwise leave it as is.

Connect to your output dataset

When constructed as a pivot table as you described in the beginning, it should look like this:

Hope this helps.