Calculate Sum between two dynamic dates

Question & Answer
,
1

Hello,

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.

image
image786×519 57.7 KB

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?

2

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.

3

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

ifelse(
extract(‘DD’,date) > 25,
truncDate(‘MM’,addDateTime(1,‘MM’,date)),
date
)

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