Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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.
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.
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!
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |