I am trying to create monthly totals for a table with daily data, and my attempts so far have not led to success. Does anyone know how to do the following?
The table has a date column, with consecutive dates for four years, a column time t starting at 0, and (as a total from several other columns) the column total users.
Based on the column total users I managed to create a column new users which is the increment in users at time t compared to time t-1, with the following formula:
*If(thisRow.[time t]=0 ,thisRow.[users-total], *
(Filter(thisTable, [time t]=thisRow.[time t]-1).[users-total]
Now, I would like to be able to create a column, either in a separate table or the same table, that shows the monthly increments in users.
I’ve been trying to find a way to add up the new users column per month/year, or, alternatively to subtract total users at the start of each month from total users at the end of each month (user total does not decline at any point, it is always increasing).
The closest I’ve come is the following, but it is slightly off because the formula can only work with every month having 31 (or every month having 30) days:
[time t]=thisRow.[time t]-31).[users-total]
Any help and ideas are appreciated, thank you!