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?

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 132 members 1,780 guests
Please welcome our newest community members: