Forum Discussion
Helper IISplitting contract amount over multiple years and months
Hi,
I am struggling to create a measure that will calculate the sum of the contracts correctly. There are contracts longer than 1 year and currently I have a measure that adds up the cost of items but it's based on the invoice date not the length of the contract.
I have created an inactive relationship between the KeyExpiryDate and the Date table I have; the other active relationship between Date Table and KeyInvoiceDate I already use it in my report.
As an example the invoice above is for a 3 year contract and currently I am showing the $3452 in December 2023, but I would like a measure that takes the item amount row by row from the table and spreads it monthly on these 3 years. Is that possible somehow?
Plese see here a sample of my data in PBI.
Kind regards
Zosy
OK done
I choosed another contract after checking the one you indicated to double check that it works but please make checks and I can fix in case further
See my result
The pbix modified is here
https://drive.google.com/drive/folders/1Yh9ltc_AdI0S1_zUcq7yvQnePHznNCij?usp=sharing
If this helped, please consider giving kudos and mark as a solution
me in replies or I'll lose your thread
Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
8 Replies
- FBergamaschi
Super User
Very nice post, along with data. Thanks
I see this, playing with your .pbix and selecting the invoice you mentioned in the post
So we see only one bar for one month, dicember 2023, as that is the start of the contract but you do not want to see the data in a single month. Am I right?
You want to see, month by month from dic 2023 up to dic 2026 the amount of the ocntract evenly divided in each month of contract duration?
A final thing: each row of the Invoices table refers to a different contract? Or can the contract be the same for multiple invoices? I do not see a contract ID to understand this.
Best
If this helped, please consider giving kudos and mark as a solution
mein replies or I'll lose your thread
Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
ZosyHi,
Yes, that is correct, the value should be spread across 36 months from dec 2023 up to dec 2026.
Apologies if my headers are confusing. I don't have a contract ID, maybe you could add an index column for a unique ID if needed.
One invoice can have multiple items (products), the contract dates are for them.
Normally the expiry date would be the same for all the products on an invoice, but sometimes supervisers process them manually and 1 or 2 items can sneak in with a different expiry.
Hope this helps- FBergamaschi
It does help but what I am not yet clear about (just notice that) is if you want the amount of a row in in the Invoice table (a certain invoice for a certain product) to be splitted all along the contract months (including the past) or only from the invoice month included up to the end of the contract.
Can you please clarify this last point?
Thanks
- Joe_Barry
Solution Sage
Hi Zosy
My solutuion requires getting the monthly amount of the contract.In Power Query, you will need to do a calculation.
Highlight the Months column, then Transform Tab > Scientific > Absolute Value this will remove the minus before the number. If you need to keep the data as is, then create another column.
Add a column and enter [Item Cost] / [Months]. Convert to Decimal Number
Load your data
Create a measure
VAR MaxDate = MAX ( DIM_Date[Date] ) RETURN CALCULATE ( [Contract Monthly Amount], KEEPFILTERS ( 'YourTableName'[Contract Start] <= MaxDate && 'YourTableName'[Contract Expiry Date] >= MaxDate ), ALL ( DIM_Date ) )Add Year and Month columns from the DIM_Date table to a visual then the measure
Hope this helps
Joe
