I have a set of records representing timesheet entries that have column headers for Date, Person who logged time, and Number of hours logged. I want to use these records to give the sum of hours per person per month however I do not want to use calendar months but rather a representation of months as month 1, month 2, month 3 etc. Therefore I'd like the dates in the Date column converted from dd-mm-yyy to show Month 1, Month 2, etc. based on the calculation as follows: Month 1 = the earliest date in among the records up to and including the date that is the earliest date in the records plus 1 month, minus one day. So, for example:
If earliest date is 21-06-2023, then Month 1 will replace all dates from 21-06-2023 to 20-07-2023. Month 2 will become 21-07-2023 to 20-08-2023, etc.
Ultimately I want these totals per month to display in a Looker Studio pivot table showing total hours per person per month. I realise it may not be possible to create this in Looker Studio so would then look to apply the conversion in BigQuery.
I have not tried to solve this