I’m working on a Monthly Recurring Revenue claculation based on the invoices. The key thing here is that the invoices may be issued once a year or on a monthly basis. The sample data looks like this:
In general, the invoice to Klient_1 should be splitted across the time of 12 months (based on the column Amount which may be treated as a amount of periods) starting from April 21 to March 22. On the other hand, all the monthly invoices should be fully allocated to the month of the invoice (no need to split a monthly invoices between two months pro rate).
I was trying the formula MRR = CALCULATE( SUM(Input_Invoices[MRR Rate]), FILTER(Input_Invoices, Input_Invoices[MRR Start Date] >= MIN(Periods[Period Start Date]) && Input_Invoices[MRR End Date] <= Max (Periods[Period End Date])))
Hi Aldek_U, based on the problem you depicted. I formulated a solution as in the attached .pbix file. You will find “Monthly Due Query” a custom query function that maps input info of a given invoice (TotalDue as number, StartDate as date, MonthCount as number) to a monthly due table. “Monthly Due” table is generated by using that function to basically map all invoices to monthly recurring due schedule (table). A “Recurring Revenue” measure is then constructed to allow summarize to your second table. Please let me know if you have any question. --YiSplit revenue.pbix (109.5 KB)
Sure deltaselect, you can just go to the Power Query Editor, click New Source, click Blank Query then Advanced Editor, copy and paste the following then Done.
let fnMonthlyDue = (TotalDue as number, StartDate as date, MonthCount as number) as table =>
let
ListN = List.Numbers(0, MonthCount),
TableFromList = Table.FromList(ListN, Splitter.SplitByNothing()),
ChangedType = Table.TransformColumnTypes(TableFromList,{{“Column1”, type number}}),
RenamedColumns = Table.RenameColumns(ChangedType,{{“Column1”, “MonthsToAdd”}}),
InsertDates = Table.AddColumn(RenamedColumns, “Month”, each Date.AddMonths(StartDate, [MonthsToAdd]), type date),
InsertMonthlyDue = Table.AddColumn(InsertDates, “Monthly Due”, each TotalDue / MonthCount, type number)
in
InsertMonthlyDue
in
fnMonthlyDue
Thanks, copy in Advanced Editor I know already.
Could you teach how you make this (kind of) query / where are the buttons for this; I want to be able to create similar queries myself. Thanks again.
I am actually not sure. I edited this code by hand and copied and pasted into the advanced editor. If someone knows other ways, please let us know. Thanks.
Hi @deltaselect,
Start a new topic and ask the question. its known as m-code. programming language that is used in the background of power bi. I don’t know much about it either but others in the forum will know
thanks
keith
I think that you can not calculate the Total Revenue per period, as you only have the invoice date, there is no revenue date . It is easy to calculate the revenue from invoices for the coming months, but how do you apply the right revenue dates per revenue-part, to be able to sum the revenue with the correct date/periods?
Maybe I am wrong, anyway I like the brought-up solutions creating a revenue fact table with the revenue dates with a query.
Just a remark:
as you defined your periods as full months for every period (eg 01 Apr - 30 Apr), in my opinion simple date table, including a column Month and Year (or Year - Month) will be sufficient, you do not need the Period table and you do not need the columns MRR Start, MRR End, MRR Start Date, and MRR End Date in your FactTable , this cleans up your Facttable significantly.
The column Date in the FactTable will be sufficient, connected to the Date Table you can derive your Montly Periods.
Hi @Aldek_U, did the response provided by the users and experts help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!
@sprmnt21 - at the moment I do not get your solution. I need to dig into it a little bit but generally the idea to deal with it in PQuery seems so obvious that for sure I will try it Especially if followed by unpivot as @HASSAN_BI suggested. Much appreciate both of you.
@Yi.Zhang - thanks for your solution. I will test it and let you all know how it fits to my situation.
In the meantime I got the proposed solution in DAX from one of my friend. It is quite impressive but something went wrong when the data were updated so here as well I will be working on it. Neverthless I share with you the DAX formula
MRR =
VAR FirstDateInContext =
MIN ( Dates[Date] )
VAR LastDateInContext =
MAX ( Dates[Date] )
VAR InvoiceDates =
CALCULATETABLE (
SUMMARIZE ( Input_Invoices, Dates[Date], Input_Invoices[Amount] ),
REMOVEFILTERS ( Dates )
)
VAR InvoiceDatesFiltered =
FILTER (
InvoiceDates,
FirstDateInContext >= Dates[Date]
&& LastDateInContext <= EOMONTH ( Dates[Date], Input_Invoices[Amount] - 1 )
)
VAR Result =
SUMX (
InvoiceDatesFiltered,
CALCULATE ( SUM ( Input_Invoices[Value] ) ) / Input_Invoices[Amount]
)
RETURN
Result
HI @Aldek_U I hope that you are having a great experience using the Support Forum so far. We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!
Hi @Aldek_U, did the response provided by @deltaselect help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!
Hi @Aldek_U, we’ve noticed that no response has been received from you since the 27th of April. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.
Hi @Aldek_U, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.
We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!
Especially if followed by unpivot as