Periodic Billing

[Greg_Deckler]

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 = 
SELECTCOLUMNS(
    FILTER(
        GENERATE(
            tmpBilling,
            SUMMARIZE(tmpCalendar,[Year],[Month],[MonthYear])
        ),
        [MonthYear] >= [MonthYearBegin] &&
        [MonthYear] <= [MonthYearEnd]
    ),
    "Customer",[Customer],
    "Year",[Year],
    "Month",[Month],
    "Amount",[Amount]
)
RETURN SUMX(tmpTable,[Amount])

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

[quilesoa]

Hi, 

Thanks for your point of view, both the example and the book have been very useful. However, I've found a case were I cannot find a solution. You specify that the table of tickets and the calendar table are NOT connected, but what would you do in the case they are? I need a common filter for the creation date calendar date but creating a relation breaks the measure.

[marsorre]

This is one of the big problem I was fighting against since the first time I've started using PowerBI and DAX. The solution it's absolutely impressive for its semplicity and elegant.

Thank you for sharing it, really appreciated.

[Elvi_well]

Hello! 
thank for sharing!
I have a question. I have a similar problem, just my payment is once per year for all 12 months. The question is when I open your pbix all works as it should, but when I use FORMAT to get YearMonth I've got a mistake "could not convert text to number" that is the reason why FILTER does not work. Did you do somthening special to fix it?

Thanks!