See attached PBIX, a dummy file of invoices. (Invoicenr, Invoice value, Months of revenue, Date, Client), with invoices having more than one month revenue in the future. For example, if Months of Revenue is 12, it means that the revenue is to be split over the future 12 months; when 1, the revenue belongs to the current month. (The revenue of the future months is calculated as Invoice amount / Months of revenue)
I would like to have a query which creates a revenue fact table from the invoice fact table, with Revenue Date per month, Client, Revenue per month and Invoice number. This query should also work for renewed data.
I received already some code, but regrettably have too much problems with the M code syntax to get it to work, could anyone help me out ?
Another solved topic on the forum was about some wrong double sales order data, which needed to be cleaned up, which was completely solved with a kind of M-code query. Could anyone provide me of some links where is explained how to develop such queries ? (preferably explained in the simplest possible way) ?
You’re in luck. Enterprise DNA Expert @haroonali1000 led a Problem of the Week earlier this year focused on this exact issue. If you go through this thread, it will have his writeup, his solution and solution video, as well as a bunch of other solutions (with PBIX files) from other EDNA members and experts. I’m confident that these materials should have all the info necessary to meet your requirement, but if you still run into any problems, pl;ease just post an updated work-in-progress PBIX file and highlight where you got stuck.
Also, here are a couple of videos responsive to the second part of your question. For learning M, I cannot recommend EDNA Expert @Melissa’s videos highly enough - I think they are truly the best resource on the internet for learning Power Query and M code:
Another great resource is Gil Raviv’s book on Power Query and M, which I think is by far the best book available on this topic. It has an excellent introducton to writing M code.
Finally, here’s a video I did recently that provides an introduction to the “Learn by Googling” appproach to M code that I learned from Melissa and have found enormously helpful:
It’s great to know that you are making progress with your query @deltaselect. Please don’t forget if your question has been answered within the forum it is important to mark your thread as ‘solved’. Also, 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!
Thanks a lot, this is exactly what I was looking for ! And it works, as you know, perfectly !
Other questions which appears with me:
Is it true that to report monthly revenue and invoicing periods in the right sequence, you need to create an unique period table with an sequence index, (as you can not sort monthly periods in the date table) ?
See below screenshot, how to avoid irrelevant cumulatives ? Adjust the DAX formula of cumulative revenue ? (Filtering out months is not dynamic)
Attached also the PBIX file, thanks again for all the support.
Thanks again, Melissa ! learned another 2 things ! (DAX und column sorting for visuals)
It sounds bit weird to me that column Year Month can not be sorted by the date, as the date seems to have, like an index, the perfect sequence in this case.
I now understand from you, that you can sort the column Year Month for the visuals by selecting MonthnYear, a many by many selection that works.
It works perfect, so I will use it !
Along these lines, one thing you might find helpful is a “cheat sheet” that I developed to accompany Melissa’s awesome Extended Date Table. One of the things it provides is guidance for each field in the date table, what other field should be used to sort it.
Here’s the thread where you can obtain the cheat sheet free from Enterprise DNA:
Enterprise DNA uses a number of free resources as marketing leads (the DAX Reference Guide, the DAX Optimization Guide, The Extended Date Table Cheat Sheet, etc.). Just click on the link below, scroll down to the Cheat Sheet section, click the button and enter your email address and they will automatically email you a copy of the cheat sheet.