Need to create calendar or new table where Value field changes based on Modified Date
I have a dataset that I need to use to derive a calendar or expanded table for every day of the year for multiple calendar years.
An example data table is shown below.
For ID = 1, I need the 'Value' field to be 4 for everyday before 1/1/2020 until the 'Modified Date' of 5/27/2020, when the Value changes to 6. Then, I need the Value to be 6 starting on 5/27/2020 and every day after that. How would I create a calendar or new table for multiple different IDs with different Values and different Modified Dates?
Thank you @MayilVahanan. Do you know of an easy way to update the code so that the "Date" only shows up once for when the "Value" updates? Right now, it shows up twice. In the example below, I would only want 5/27 to have the value of 6.