Split amount based on date

[Khalidc]

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 · Accepted solution]

@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]

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)