ETL - How to spread a Budget over two dates within a variable date range.

Gojo

I have various start and end dates for campaigns. I know how to calculate the # of days between any get set of dates. But, what I am trying to set up in the etl is this -  if I have a budget amount, start date and end date, what is the easiest way for me to have the etl calculate the division of the budget over the given start and end dates?

e.g. let's say I have a $9800 budget, and I set the start date/end date  for 4/17/2024 - 10/12/2024. I want it evenly distributed over the number of days, etc.

I would want the to know a couple metrics from these inputted values,

The Daily Budget

The Monthly Budget (taking into account that April and October are not full months)

And those values projected for future months.

Is there a way to set this up in the ETL?

david_cunningham

The easiest way would be to use a date dimension table and a cross join (Cartesian Product). Say you have your budget dataset.

You would join your date dimension table (a dataset that has a row for every day) with your budget dataset.

You would then filter to where dt (the day in your date dataset is >= start_date and < = end_date.

You can then calculate the number of days between your start and end dates. Note, you could also make use of the "Date Operations" tile if you wanted the number of working days between these dates. Then divide your total budget by the number of days to get the daily budget.

For the monthly the easiest way would be to connect this dataset to a card and then aggregate the daily total by month.

For example

david_cunningham

The easiest way would be to use a date dimension table and a cross join (Cartesian Product). Say you have your budget dataset.

You would join your date dimension table (a dataset that has a row for every day) with your budget dataset.

You would then filter to where dt (the day in your date dataset is >= start_date and < = end_date.

You can then calculate the number of days between your start and end dates. Note, you could also make use of the "Date Operations" tile if you wanted the number of working days between these dates. Then divide your total budget by the number of days to get the daily budget.

For the monthly the easiest way would be to connect this dataset to a card and then aggregate the daily total by month.

For example