Showing results for 
Search instead for 
Did you mean: 

Periodic Billing

Super User
Super User
Super User

Periodic Billing

Again, thanks to @Phil_Seamark's insightful guidance and examples in his fantastic new book, Beginning DAX with Power BI: The SQL Pro’s Guide to Better Business Intelligence, I finally "get" the GENERATE function and how it can be used to elegantly solve problems that have vexed me since almost the very first Power BI model that I ever built, dealing with data that contains date ranges. This one uses the same technique as Open Tickets but puts a different spin on it by also requiring that there be a periodic element to the totals calculation.


The following measure assumes a disconnected date table and data that involves billing starting and ending dates with a monthly fee. The measure computes the total revenue within any particular month, which can then be plotted. Also nifty.



Total Amount = 
VAR tmpCalendar = ADDCOLUMNS('Calendar',"Month",MONTH([Date]),"Year",YEAR([Date]),"MonthYear",VALUE(YEAR([Date]) & FORMAT(MONTH([Date]),"0#")))
VAR tmpBilling = ADDCOLUMNS('Billing',"MonthYearBegin",VALUE(YEAR([BeginDate]) & FORMAT(MONTH([BeginDate]),"0#")),
                                      "MonthYearEnd",VALUE(YEAR([UntilDate]) & FORMAT(MONTH([UntilDate]),"0#")))
VAR tmpTable = 
        [MonthYear] >= [MonthYearBegin] &&
        [MonthYear] <= [MonthYearEnd]
RETURN SUMX(tmpTable,[Amount])



Again, if you are only going to own one DAX book, IMHO, Phil's is the book you want!



I have book! Learn Power BI from Packt

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!