# Calculate remaining days of the month

Hi ,

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.

5replies Oldest first
• Oldest first
• Popular
• Can you not handle that in DB/at source rather than doing in ThoughtSpot?

Like 1
• Using formulas

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

data model

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

Like 1
• 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.

Like 1
• Dave Eyler Thank you so much for the solution. This solves my problem.

Like
• Thanks Siddhi!  I think I have to give some credit to Dave Cohen on this one...

Like 1
Like Follow