Calculate remaining days of the month
I have a requirement to calculate projected cost.
Projected Cost = Month to Date Cost + ( Avg (Month to Date Cost) * remaining Days)
I have a Date Column and Cost Column.
I am unable to get the right function/formula to calculate the remaining days of the month.
Step 1 : determine end of month. This would require combination of a number of formulas as we don’t have end of month: start_of_month, add_months,add_days, today
step 2: use date_diff between current date and formula from step one
Create a date table with two columns for every date where column one has a value if 1 for every dates todate ie future dates have zero . Column two has a value of 1 for every date . Then you can sum these numbers to return days todate, total days and calculate the difference for remaining
Siddhi Doshi - try this formula:
DaysLeftInMonth: diff_days ( start_of_month ( add_months ( today ( ) , 1 ) ) , today() ) -1
It's easiest if you read this from the inside out:
(1) Add one month from today: add_months ( today ( ) , 1 )
(2) Get the first day of the next month: start_of_month ( add_months ( today ( ) , 1 ) )
(3) Then, take the difference in days from the start of next month to today's date, and subtract 1
To test it for a number of different dates, you can use these 2 formulas:
DateToTest: to_date ( '11/05/2019', '%m/%d/%Y')
DaysLeftTest: diff_days ( start_of_month ( add_months ( datetotest , 1 ) ) , datetotest ) -1
DateToTest | DaysLeftTest
11/5/2019 | 25
11/29/2019 | 1
12/30/2019 | 1
12/31/2019 | 0
2/28/2020 | 1 (2020 is a leap year)
2/29/2020 | 0
Let us know if this works out for you.