How to allocate days into months using a start and end date as a measure

[Anthony]

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

[sam.mckay]

Hi Anthony,

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.

See below

Calculate The Occupancy Days Per Month In Power BI Using DAX

This came from a recent forum post here

How to create new measure that calculates occupancy per month between two date columns?

One thing I would recommend here first is actually attempt to give it go in the model. The best way to learn is to just start writing DAX, create models etc. It will also speed up how we can assist you. First you need a date table (always). Not going to get very far without one here. [How To Create A Detailed Date Table In Power BI Fast] What you ultimately need to solve this is derivative of the ‘events in progress’ pattern that can be found below. It’s all about managing multip…

See how you go with these.

Thanks
Sam

[Anthony]

Hi,

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

Thanks

Apportion Data Sample.pbix (99.1 KB)

[sam.mckay]

I think this is all you needed to change here

Allocate Days = 
VAR InvoiceStart = [Min Date]
VAR InvoiceEnd = [Max Date]
VAR MinDateInContext = VALUE( MIN( Calender[Date] ) )
VAR MaxDateInContext = VALUE( MAX( Calender[Date] ) )

RETURN
IF( AND( InvoiceStart < MinDateInContext, InvoiceEnd > MinDateInContext ),
    MIN( InvoiceEnd,MaxDateInContext ) - MinDateInContext,
        IF( AND( AND( InvoiceStart > MinDateInContext, InvoiceStart < MaxDateInContext), InvoiceEnd > MinDateInContext ),
            MIN( InvoiceEnd, MaxDateInContext ) - InvoiceStart,
                BLANK() ) )

Because you were already calculating these numbers I just subbed them in to see what would happen.

I think the measure wasn’t picking up the dates correctly using the existing logic.

See how you go with this.

Thanks
Sam

[Anthony]

Hi

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

Thanks

Anthony

[sam.mckay]

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.

Thanks
Sam

[Anthony]

Hi Sam, thanks for your help on thos worked out a solution using some branched measures

[sam.mckay]

Ok that’s great.

[ignacio.pujana]

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.

How would you go about it?