How can I calculate a monthly total given a total amount, a start date and an end date.
For example: I have a total amount of $12,000 with a start date of 03/01/2021 and an end date of 08/31/2021 and I would like to show the amount per month as follows:
03/2021 04/2021 05/2021 06/2021 07/2021 08/2021
$2,000 $2,000 $2,000 $2,000 $2,000 $2,000
If you want to show them as separate months you need to create the data by splitting the start & end into months. I normally do that using calendar table, join the two dates, get a result as a list of months and then use the total / count of months in that group (that will be a window function).
Thank you Dawid! I will give that a try!