helpIf the table looks like this:
03/01/2022 | 8 |
03/03/2022 | 3 |
03/04/2022 | 3 |
03/15/2022 | 5 |
03/21/2022 | 7 |
04/01/2022 | 6 |
04/15/2022 | 1 |
04/18/2022 | 9 |
What would be the formula to get the sum of the previous month from the current month date?
Right now, I have the previous month's sum formula as:
IF ToNumber(DateTimeMonth(DateTimeToday()))-tonumber(DateTimeMonth([spent_date])) = 1 AND DateTimeYear([spent_date]) = DateTimeYear(DateTimeToday())THEN [Sum_hours] ELSE 0 ENDIF
If the formula is correct, can someone explain why it is correct and/or if there is a better way to get the data? Thank you in advance.
The expression is correct. I encourage you to take a look at the documentation and see what each of those formulas means.
https://help.alteryx.com/20214/designer/datetime-functions
Also, here is another way of doing this, which I personally think it is simpler and easier to understand:
IF DateTimeTrim([spent_date],'month') = DateTimeAdd(DateTimeTrim(DateTimeNow(),'month'),-1,'month')
THEN [Sum_hours]
ELSE 0
ENDIF
@gabrielvilella And you can also replace
DateTimeTrim(DateTimeNow(),'month')
with
DateTimeFirstOfMonth()