I am struggling to find a solution to a problem I have, where I have many invoices covering various different periods across many different accounts.

I need a better solution to allocate the days each invoice covers to the month \ year they span (i.e if an invoice covers 01/01/2019 - 15/03/2019, expected result would:

Jan_19 - 31 days

Feb_19 - 28 days

Mar_19 15 days)

Currently my solution is to use a cross join, but it is becoming very inefficient due to the number of invoice rows and dates in my calendar table)

Ideally looking for a measure to do this for me, used the order in progress pattern but doesn’t seem to give the result I need when an invoice doesn’t cover the entire period)

In addition there will be instances where there is more then one invoice covering 1 month (i.e. invoice 1 covering 01/01/2019 - 15/01/2019 and invoice 2 covering 15/01/2019 - 31/01/2019), so need to consider this as once I can allocate the days, I then need to allocate the cost, where my thinking was to calculate a daily cost per invoice and use this

Any help on this would be most appreciated

Thanks

Anthony