Splitting contract amount over multiple years and months

[Zosy]

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

[Joe_Barry]

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

[FBergamaschi]

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

[Zosy]

Hi,

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

[Zosy]

Split only from the invoice month included up to the end of the contract would be great. 
Thnak you

[FBergamaschi · Accepted solution]

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

[Zosy]

Hi,

I have used the below and it works now. 

Contracts =
SUMMARIZE (
    'Invoices',
    'Invoices'[Invoice Number],
    "MaxStartDate", MAX ( 'Invoices'[Contract Start Date] ),
    "MaxExpiryDate", MAX ( 'Invoices'[Contract Expiry Date] )
)

Thank you so much for your help

[Zosy]

Thank you ! I tried it with my dataset but I think it needs some tweaking. As mentioned before there are old invoices where the contracts have different expiry dates. Your relationship between Invoices and Contracts is Many to 1. In mine it only allows Many to Many because of those ones. How can I modify the table below so it only has each invoice with the max start date and max expiry date?

Contracts = 
ALLNOBLANKROW( Invoices[Invoice Number], Invoices[Contract Start Date], Invoices[Contract Expiry Date] )