Get month values between start and end dates in contracts

[hooglvi1]

I am quite new with this all, but I do understand the basics of Power Pivot (and a bit about Power Query).I have a source file, which consist out of records build up like: ContractNr, Full_Amount, Start_Date, End_Date. Now I would like to have an overview of how much I have to pay in a certain month over all the contracts. Some contracts are active, some are not, however that is dependent on the start- end and date. I know I can solve this with some SQL scripting in a SQL server, however if this can be done directly in PowerPivot (based on the data model as described), that would be great. Thanks for any help, Victor

 

[scottsen]

Your problem is basically "on a given day, is this contract active?", which is similiar to this: Two methods to calculate Person-Hours | Tiny Lizard

Be sure to see the comments that refers to OTHER pages of similiar stuff.

See if that gets you in the right direction, then come back and yell at me when it doesn't

 

[hooglvi1]

Dear Scott, thank you for your help. You indeed pointed me in the right direction and I found the solution: Count the number of days in a given month and multiply with the cost per day. Many thanks, Victor