topic Re: Periodic Billing in Quick Measures Gallery

Periodic Billing

Greg_Deckler — Tue, 26 Jun 2018 17:13:16 GMT

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!

eyJrIjoiN2IyMGNlYmItZjhjNi00M2IxLWI1MDAtZmVkMzIxMjkzNmFhIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9

Re: Periodic Billing

Elvi_well — Wed, 19 Apr 2023 10:54:21 GMT

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!

Re: Periodic Billing

marsorre — Mon, 19 Feb 2024 18:49:01 GMT

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.

Re: Periodic Billing

Anonymous — Tue, 06 May 2025 14:32:22 GMT

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.