Greg_Deckler's avatar
Greg_Deckler
Community Champion
8 years ago

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 = 
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!

 

 

Mathematical
Time Intelligence

3 Replies

  • marsorre's avatar
    marsorre
    Regular Visitor
    2 years ago

    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's avatar
    Elvi_well
    Frequent Visitor
    3 years ago

    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! 

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.