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 always be in the future). For the sales forecast we need to recognize 50% of the dealvalue on closing date, and the other 50% 6 months after the closing date.

 

I have a date tabel, which has been brought in relationship with the closing.date column in the deals table.

 

The measure I have created, which doesn't bring the expected result is: 

 

Deals amount 6m split =
var currentday = SELECTEDVALUE('Date'[Row Index Column])

return

DIVIDE([Deals amount],2)
+
CALCULATE([Deals amount] ,
           filter(all('Date') ,
                'Date'[Row Index Column] = currentday + 180
            )
        )
 
The visual below shows the result. You can see that 50% of the deals amount is presented correctly, however the measure doesn't show the 50% deals amount 6 months after the closing date.

 

  • 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))

2 Replies

  • 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)