Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
aTChris
Resolver I
Resolver I

Calculate pro rated amount over a period of time

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.

1 ACCEPTED SOLUTION
Mariusz
Community Champion
Community Champion

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.

 

View solution in original post

5 REPLIES 5
Mariusz
Community Champion
Community Champion

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.

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
Community Champion
Community Champion

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.

 

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!

@Mariusz 

 

Thanks, great solution. Just what I need.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.