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

If you have a demo model to share that is always the best to get a solution fast. There can be a lot at play here included the data model you have, context of the calculation etc.

One thing though is have you watched this video here. My feeling is that the solution is quite similar to what you need. The scenario is different but the formula to solve it seems like it would suit your requirements of allocating results across different months.

Many thanks for your quick response, material you pointed me to was very useful and looks like it solved most of the problem.

Ideally I don’t want to show the invoice start and end dates, but I cant seem to replicate the matrix table you have in the occupancy example (having more than one row column on the left) and as soon as I remove the invoice start and end dates the table shows no values

I did try the sumx to sum up all the days, but doesn’t seem to work in my model

Once I get around this I then need to create a similar table \ graph apportioning the units and cost

See attached model with some sample data, ideally I would like the table \ graph to show just the supply point and the days allocation across the months, and similar when I most to units and cost

Many thanks for this, measure is bringing back the expected results for the days, in terms of getting the apportioned usage and cost, would you create new measure to do this or create a similar measure like the allocate days only calc the dailys in there? as it would need to work out the daily usage and cost at an invoice level and then sum them up (to account for instances when 2 invoices cover parts of the same month)?

Finding it difficult to understand exactly the requirement here with out seeing an example.

Say for example though you are just attempting to do something similar but add costs to the total calculation…you could just simply branch out from here and create a simple new measure which reference this existing one and adds logic to it.

Past this I probably would need to see more around the exact scenario here to assist. I’m just not 100% sure what to advise regarding formula ideas without understanding anything more.

Hi Sam, came up with your videos on the occupanacy calculation and cannot thank you enough.
Was trying to devise a way to modify the measure so It does not require listing the Patient ID in order for the calculation to occur.
Lets say I want to know the total occupancy for each month so I can make a Year over Year or Month over Month comparison.