cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
simo12447
Helper III
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?

 

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

 

Thanks a lot in advance.

Simone

2 ACCEPTED SOLUTIONS

@simo12447

 

Please see revised file attached

 

 


Regards
Zubair

Please try my custom visuals

View solution in original post

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

View solution in original post

14 REPLIES 14
Greg_Deckler
Super User
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


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

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

 

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 )

3.JPG

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:

4.JPG

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.

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

@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}}),
    #"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"

 


Regards
Zubair

Please try my custom visuals

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

 

Capture1.PNG

@simo12447

 

Please see revised file attached

 

 


Regards
Zubair

Please try my custom visuals

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

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

Hi @simo12447 

 

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


Regards
Zubair

Please try my custom visuals

Capture.PNG

 

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

Hi @simo12447 

 

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

Please try my custom visuals

 

 

Capture1.PNG

 

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

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

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

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

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

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

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

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

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!

Top Kudoed Authors