Forum Discussion

Zosy's avatar
Zosy
Icon for Helper II rankHelper II
1 year ago
Solved

Splitting 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 ite...
FBergamaschi's avatar
FBergamaschi
Icon for Super User rankSuper User
1 year ago

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's avatar
Zosy
Icon for Helper II rankHelper II
1 year ago

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's avatar
    FBergamaschi
    Icon for Super User rankSuper User
    1 year ago

    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