MonthToDate calculation over time

Question & Answer
1

hi!

I’m trying to calculate a MonthToDate comparison between different month over time.
let’s say my dataset is as follows.

transactionDate transactionVolume
06/02/2022 4000
07/02/2022 5000
08/02/2022 6000
09/02/2022 7000
10/02/2022 8000
18/02/2022 10000
06/03/2022 13000
07/03/2022 14000
08/03/2022 15000
09/03/2022 16000
10/03/2022 17000
18/03/2022 20000
06/04/2022 23000
07/04/2022 24000
08/04/2022 25000
09/04/2022 26000
10/04/2022 27000

my result should be

transactionMonth MTDVolume
Feb-22 30000
Mar-22 75000
Apr-22 125000

I defines the calculation as follows:
periodToDateSum(transactionVolume, transactionDate, MONTH).

my problem:
I get results only for current month Apr-22.
what is the function I need to use to get the right result, meaning the relative part of the month also for prior monthes?

Thanks!
Amihai

2

You can try to create a month column and then use sumOver().

3

sumOver(transactionVolume,motnt(transactionDate)) just summarize the whole prior months, regardless to the relative period in prior months.
I would like my function to summarize up until the specific current date in each month.
any ideas?

4

found a solution -
sumif(transactionVolume,ifelse(extract(‘DD’,transactionDate)<=extract(‘DD’,now()),1,0) = 1).

though, I’ll appreciate if there is a more elegant designated function for that use case.