Spread revenue across contract length

[Samuelcurr1e]

I want to be able to graphically show the expected revenue from potential contracts.

The data looks a little like the below:

Opportunitu ID= ........

Opportunity Name= .......

Contract Value= 1,000,000

Start Date= 01/01/2018

End Date= 01/01/2019

Months= 12

I want to be able to equally split the 1,000,000 across the 12 months and display that in my chart. Is it possible in DAX to do so? I have created a Date table that is linked to my dataset.

Thanks

Sam

[Zubair_Muhammad]

@Samuelcurr1e

You can create a NEW CALCULATED TABLE to expand the range of dates into ROWS

From the Modelling tab>>New Table

New Table =
GENERATE ( TableName, GENERATESERIES ( 0, TableName[Month], 1 ) )

Now you can add new Columns to your Table to get the desired Columns

Dates =
EOMONTH ( 'New Table'[Start Date], 'New Table'[Value] - 1 )
    + 1

Split_Contract_Value =
'New Table'[Contract Value]
    / ( 'New Table'[Month] + 1 )

[Anonymous]

are you able to provide the same in excel?

[Zubair_Muhammad · Accepted solution]

@Samuelcurr1e

Please see the attached file here

Hope it will give you the idea