Calculate pro rated amount over a period of time

[aTChris]

Im trying to create a table of monthly revenue from a contract record. The contract gives me a startdate, enddate and monthly amount. I use a CROSSJOIN with a calendar to create a table of monthly revenue so that I can create a visualisation from it. The CROSSJOIN does not take into consideration a partial month i.e. contract starting or ending mid month. Is there a measure I can add to the calendar table so that the CROSSJOIN will calculate the prorated amount for a partial month.

[Mariusz]

Hi @aTChris 

Can you provide some data samples?

Regards,
Mariusz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[aTChris]

Hi @Mariusz 

An example of the data is

ContractID        Start Date        End Date       Monthly Revenue

123456             01/06/2019       31/05/2020     £3000

123457             15/06/2019       31/05/2020     £2000

I use this CROSSJOIN to create a table to revenue amounts per month

Revenue = FILTER(CROSSJOIN('ContractsDeskRateData','Calendar'),'Calendar'[Date]>=ContractsDeskRateData[StartDate]&&'Calendar'[Date]<=ContractsDeskRateData[EndDate].[Date])

That creates entries for June 2019 of £3000 and £2000. When ContractID 123457 should be £1000 because the contract starts halfway through the month.

[Mariusz · Accepted solution]

Hi @aTChris 

Please see the below M function expression.

( startDate as date, endDate as date ) => let
    sYear = Date.Year( startDate ),
    sDate = #date( sYear, Date.Month( startDate ), 1 ),
    eYear = Date.Year( endDate ),
    eDate = Date.EndOfMonth( #date( eYear, Date.Month( endDate ), 1 ) ),
    listYears = List.Transform( { sYear..eYear }, each { _ } ),
    listToTable = #table( type table [ #"Years" = Int64.Type ], listYears ),
    addMonths = Table.ExpandListColumn( Table.AddColumn( listToTable, "Months", each { 1..12 } ) , "Months" ),
    getStartDate = Table.AddColumn( addMonths, "startDate", each #date( [Years], [Months], 1 ) )[ startDate ],
    selectStartDate = List.Select( getStartDate, each (_) >= sDate and (_) <= eDate ), 
    output = List.Transform( selectStartDate, 
        each 
        Record.FromList(
            {   
                ( Number.From( 
                    Duration.From( 
                        ( if Date.EndOfMonth( _ ) > endDate then endDate else Date.EndOfMonth( _ ) ) - 
                        ( if ( _ ) < startDate then startDate else ( _ ) ) 
                    ) 
                ) + 1 ) / ( Number.From( Duration.From( Date.EndOfMonth( _ ) - ( _ ) ) ) + 1 ) 
                , ( Date.Year( _ ) * 100 ) +  Date.Month( _ ) 
            }, type [pct = number, YearMonth = Int64.Type]  

        ) 
    )  
in  output

Please use "Invoked Custom Function" to add column pass your start and end dates as paramiters and expand list and than records, you will get two columns pct and Year Month, multiply  pct and Monthly Revenue 

Regards,
Mariusz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bradbruno]

Hi Mariusz - I have a similar issue but I need a solution in DAX/ measure(s) beacause I have like too many schedule rows (over 5million) and expanding in Power query would be slow/too big.. vs on the fly measure to do the calc.. any help woruld be awesome!

[aTChris]

@Mariusz 

Thanks, great solution. Just what I need.