Backlog contract (Spread revenue across contract length) Power Pivot

[Anonymous]

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?

I read this article  Read here but I am not able to replicate. Anyone can help me? 

Thanks a lot in advance.

Simone

[Zubair_Muhammad · Accepted solution]

@Anonymous

Please see revised file attached

[Greg_Deckler]

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

[Anonymous]

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. 😞 

[v-lili6-msft]

hi, @Anonymous

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

[Anonymous]

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

[Zubair_Muhammad]

@Anonymous

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}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Int64.From(Duration.Days(Duration.From([#"EndContractDateKey "]-[#"StartContractDateKey "]))/30)),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Custom", "No of Months"}}),
    #"Added Custom2" = Table.AddColumn(#"Renamed Columns", "Value per Month", each [#"Value "]/[No of Months]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom2", "Months", each {1..[No of Months]}),
    #"Expanded Months" = Table.ExpandListColumn(#"Added Custom1", "Months")
in
    #"Expanded Months"

[Anonymous]

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

[Zubair_Muhammad · Accepted solution]

@Anonymous

Please see revised file attached

[Anonymous]

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?

thanks a lot in advance.

Simone

[Anonymous]

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

[Zubair_Muhammad]

Hi @Anonymous 

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

[Anonymous]

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

[Zubair_Muhammad]

Hi @Anonymous 

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

[Anonymous]

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

[Anonymous · Accepted solution]

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