Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
@Anonymous
Please see revised file attached
You are a monster! Chapeau! Thank you sooooo much... you saved so much of my time!!!
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
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, @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
@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?
@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"
@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...
@Anonymous
Please see revised file attached
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 @Anonymous
Could you share/illustrate expected results with this new requirement?
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 @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
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!!!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |