Periodic Billing 05-02-2018 17:34 PM - last edited 06-26-2018 10:13 AM

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

eyJrIjoiN2IyMGNlYmItZjhjNi00M2IxLWI1MDAtZmVkMzIxMjkzNmFhIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9

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