Calculate Sum between two dynamic dates


Monthly calculations in our company is not by calendar month, but they are performed from 26th of month to 25th of next month.

I know we can change the start date of the week. Is there a way to change to start date of the month so I can default all the calculations to Billing Month instead of Calendar month?

I also tried doing this via calculated fields, so far I am able to calculate Billingstartdate and BillingEnddate for each calendar month.

However I am not able to calculate Sum of values between these two dates

This is how I am able to calculate between two dates

sumif(smsCount,date>=BillingStartDate AND date<=BillingEndDate)

But the calculation only includes data for current month 25 days, it does not take into account data for previous 5-6 days of previous month.

What am I missing here?

Hello @Garima_Gupta, instead of using the sumIf calculation, I would use an ifelse and then use the sum aggregation on the result, or you could even utilize a sum over. Create a calculated field that looks like this:
ifelse(date>=BillingStartDate AND date<=BillingEndDate, smsCount, NULL)

That will make sure you are only receiving the smsCount for the date values that you want and you can aggregate on the return value of that calculated field.

Hi Dylan, I tried and that did not work either. I ended up adding another calculated date column - BillingDate with the following calculation

extract(‘DD’,date) > 25,

Above formula essentially updates the dates of previous month dates after 25th to next month.
Now I am able to use BillingDate column to get values based on my company’s billing date.

This also comes in handy because now I can dynamically toggle between billing month and calendar month data.

1 Like