cancel
Showing results for
Did you mean:
Helper III

## Backlog contract (Spread revenue across contract length) Power Pivot

Hi all

In my backlog I have normal orders and contracts. For the contracts imagine this scenario..

StartContractDateKey 1/1/2017

EndContractDateKey   1/1/2019

Value                           1,000,000

I want to be able to equally split the 1,000,000 across the 12 months and determine how much is open for each future months.  What is the formula? Can anyone attach a super simple model in excel to explain?

Simone

2 ACCEPTED SOLUTIONS
Super User

@simo12447

Regards
Zubair

Helper III

You are a monster! Chapeau! Thank you sooooo much... you saved so much of my time!!!

14 REPLIES 14
Super User

Take a look at these two Quick Measures as I think you want something like them.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Learn Power BI 2nd Edition
Helper III

is this formula going to give me the split of the 1,000,000 across the contract period?

I m not completely follow the formula. 😞

Community Support

hi, @simo12447

You could try this steps as below:

Step1:

Do you want to equally split value across specify the number of months or the months between StartContractDateKey with

EndContractDateKey?

you could use this formula to calculate months between StartContractDateKey with EndContractDateKey

`Months2 = DATEDIFF(data[StartContractDateKey],data[EndContractDateKey],MONTH )`

Step2:

Modeling->New table

`New Table = Generate(data,Generateseries(1,data[Months2],1))`

Step3:

for this new table add two new column

`Dates = EOMONTH ( 'New Table'[StartContractDateKey], 'New Table'[Value] -2 )+1   `
```Split_Contract_Value =
'New Table'[Values]
/ ( 'New Table'[Months2] )```

Result:

here is pbix please try it.

https://www.dropbox.com/s/x3y2x2fhk7hjcly/Backlog%20contract.pbix?dl=0

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper III

@v-lili6-msft thanks a lot. I get the logic.. but I do not user power bi... how should I do in power pivot in Excel?

Super User

@simo12447

You can do it with Excel's Power Query

Please see if the attached file is useful

You can follow the steps from Query Editor

```let
Source = Excel.CurrentWorkbook(){[Name="Table1_2"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"StartContractDateKey ", type datetime}, {"EndContractDateKey ", type datetime}, {"Value ", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Custom", "No of Months"}}),
#"Added Custom2" = Table.AddColumn(#"Renamed Columns", "Value per Month", each [#"Value "]/[No of Months]),
#"Expanded Months" = Table.ExpandListColumn(#"Added Custom1", "Months")
in
#"Expanded Months"```

Regards
Zubair

Helper III

@Zubair_Muhammad Thanks!!! You are good!!!!! I think this is solving half of my problem. I am missing how to show only what is left.

As per the example below.. I need to tell them how much is left for the future periods and categorize the future periods as month 1, month 2, month 3...

Super User

@simo12447

Regards
Zubair

Helper III

Master ... I have 1 more question... what should I do if I want the actuals months as aging instead of number of days? i.e. Feb has 28 days.. so for my backlog it would be cool to understand how much backlog I have in the month of feb instead of in the next 30 days. do you have any clue?

Simone

Helper III

@Zubair_Muhammad  Hi Zubair, sorry to bother you again.. but do you have any idea on how to solve the below? thanks so much!

Super User

Could you share/illustrate expected results with this new requirement?

Regards
Zubair

Helper III

Hi @Zubair_Muhammad last time we talked.. you were able to show me how to break it down by aging bucket.. but I need to understand what it is burning in the month of April 19/ May19/ June19 etc because if I am looking at my backlog I may have months with 30 days... or months with 31 days or days with 28..  so what I am looking is to link the aging with the calendar.

let me know if this is clear.

thank you soooo much!
Simone

Super User

If you could copy paste some sample data (Copiable format) (Few rows of raw data)

and the expected output with this data....it will help me to try,test and match the results

Regards
Zubair

Helper III

the database is the one you attached me 3 answers above.

Helper III

You are a monster! Chapeau! Thank you sooooo much... you saved so much of my time!!!

Announcements

#### The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

#### Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!