Forum Discussion

Khalidc's avatar
Khalidc
Frequent Visitor
4 years ago
Solved

Split amount based on date

Hi    For a sales forecast report I need to calculate the future sales based on dealvalues and the closing date of a deal. The closing date is the date that the deal is expected to be closed (will ...
  • amitchandak's avatar
    amitchandak
    4 years ago

    Khalidc , Assume you have Table with Deal amount and close date

    A new columns

     

    Addcolumns(
    generate(table, filter(calendar(close_date, eomonth(close_date,6)), [Date] = eomonth([Date],0))),
    "New Deal Amount" , if(eomonth([Close_date],0) = [Date], [Deal Amount]/2, [Deal Amount]/12))

amitchandak's avatar
amitchandak
Super User
4 years ago

Khalidc , Assume you have Table with Deal amount and close date

A new columns

 

Addcolumns(
generate(table, filter(calendar(close_date, eomonth(close_date,6)), [Date] = eomonth([Date],0))),
"New Deal Amount" , if(eomonth([Close_date],0) = [Date], [Deal Amount]/2, [Deal Amount]/12))

  • Khalidc's avatar
    Khalidc
    Frequent Visitor
    4 years ago

    Hi amitchandak 

     

    Many thanks for your help! I did have to make a slight adjustment on your suggestion but it helped into the right direction. Your solution provided a spread of the amount over the 6 month, but I needed 50% recognised at closing and 50% after closing. See below the adjustments I have made in red. 

     

    Many thanks for your help!

     

    I have created a new table using below measure, which has done the trick for me.

     

    Hubspot_dealvalues +6m split =
    filter(ADDCOLUMNS(
    GENERATE(HubSpot_Objects_Deals_Information,                    filter(CALENDAR(HubSpot_Objects_Deals_Information[Closedate],EOMONTH(HubSpot_Objects_Deals_Information[Closedate],6)
                                    ),[Date] = EOMONTH([Date],0))
                        ),
                        "new deal amount", if (EOMONTH(HubSpot_Objects_Deals_Information[Closedate],0) = [Date], [Deals amount] / 2 , if (EOMONTH(HubSpot_Objects_Deals_Information[Closedate],6) = [Date], [Deals amount] / 2 , BLANK())), [deals amount]>0)