Proration-factor - calculate days within period compared to total duration

[Anonymous]

So a little background;

I am working with data for vessels and their voyages. Each voyage will have a start date and an end date, and I want to calculate the number of days within the chosen period as a proportion of the voyage total days.

This is for prorating within a chosen period, typically financial results within a Quarter, specified with a Slicer. 

To make it most flexible, I'm considering using a factor which could be applied to any data to represent the prorated values within the period.

One example:
Voyage starts March 15th and ends June 20th.

Period covers month 4-6 (2nd quarter):

Days before chosen period = 16
Days within chosen period = 91
Days after chosen period = 20
Total days = 127

I would then want to return the factor 91/127 = 0,7165

I've tried several IF-functions, but cannot get it to work properly.

Below is some sample data. End result should be the factor. 

Any tips on how to solve this is highly appreciated. 

[mahoneypat · Accepted solution]

If you don't have one already, you should make a Date table that has a Quarter column.  For this analysis, you don't need a relationship between the tables, but you probably will for other analyses.  Below is a DAX measure expression you could adapt for your need.  It assumes you are in a matrix visual with Voyage on rows and Quarter on columns.

Pct Within Period =
VAR mindate =
    MIN ( Date[Date] )
VAR maxdate =
    MAX ( Date[Date] )
VAR totalvoyage =
    SUMX ( Voyage, Voyage[End] - Voyage[Start] )
VAR voyagestart =
    MIN ( Voyage[Start] )
VAR voyageend =
    MIN ( Voyage[End] )
VAR calcstart =
    IF ( mindate < voyagestart, voyagestart, mindate )
VAR calcend =
    IF ( maxdate > voyageend, voyageend, maxdate )
VAR daysinperiod = calcend - calcstart
RETURN
    DIVIDE ( daysinperiod, totalvoyage )

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

I get this to work per voyage/row. 

However I cannot make it work on aggregated levels, meaning per vessel. 

Please consider below data and setup: 

This is the relationship between the tables: 

I would then like to present a sum per vessel by vessel name. Key point here is the sum of the two voyages for Ship3. 

So something like this: 

When I try this now, I get no results. 

[Anonymous]

Thanks! 

It brings me 95% of the way - added a few tweaks for filtering a bit, but otherwise I'm nearly there. 

I will work a bit more on this concept, but this is the closest I've ever been to a solution. 

Main issues going forward is to be able to maintain filters when I combine data from different tables in the same Table visual. 

I end up with alot of blank rows when adding columns from other tables, but expect that to be an issue with missing relations and filtering.