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

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

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

  • FBergamaschi's avatar
    FBergamaschi
    Icon for Super User rankSuper User
    1 year ago

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

      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] )

       

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

      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