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.

 

Please advise. 

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

    Reply 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  

    Reply 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.

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

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

    Reply Like 1
Like Follow
  • Status Answered
  • 3 days agoLast active
  • 5Replies
  • 40Views
  • 5 Following