cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Samuelcurr1e Frequent Visitor
Frequent Visitor

Spread revenue across contract length

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Spread revenue across contract length

@Samuelcurr1e

 

Please see the attached file here

 

Hope it will give you the idea

3 REPLIES 3
Super User
Super User

Re: Spread revenue across contract length

@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 )
Super User
Super User

Re: Spread revenue across contract length

@Samuelcurr1e

 

Please see the attached file here

 

Hope it will give you the idea

simo12447 Frequent Visitor
Frequent Visitor

Re: Spread revenue across contract length

are you able to provide the same in excel?