Calculate Fees Due from Duration Range without Calendar Table Relationship

[tachydidaxy]

Not sure how to even begin thinking about this, hoping for some insight or suggestions.

I have a table of fees/dues per customer/member called Dues (screen grab below for reference):

This table simply lists a start date and an end date for the dues/fees, also the amount. There is another column that gives the bill_frequency, or number of times in a given calendar year the fee/due is charged.

Because this is using a time range or duration, i.e. from 05/01/2013 to 07/31/2018, I can't create a relationship with my calendar table.

My goals are two-fold:

1. Measure that returns the total dues/fees charged by Year and Month called [Total Dues]

2. YTD Measure called [Total Dues YTD]

I'm struggling because I can't link this to the calendar table, so I have no idea how I'd make the YTD measure.

But, before that even, I'm stuck on the simple calculation of [Total Dues].

I've tried a start as shown below:

Total Dues = 
CALCULATE (
    SUM ( 'Dues'[amount] ),
    FILTER ( 'Dues', 'Dues'[from_date] <= CALCULATE ( MAX ( 'Calendar'[Date] ) ) ),
    FILTER (
        'Dues',
        ISBLANK ( 'Dues'[billed_upto] )
            || 'Dues'[billed_upto] >= CALCULATE ( MIN ( 'Calendar'[Date] ) )
    )
)

That measure produces this when dropped into a matrix using year and month at the row level:

But that is a feeble start, that simply sums the amounts for fees/dues that are active and does not account for the bill_frequency needed (i.e does not account for quarterly vs monthly dues etc.)

Any advice and suggestions are welcome.

Cheers.

[Ashish_Mathur]

Hi,

I have solved a similar question - Distribute projected revenue annually.

Hope this helps.

[Greg_Deckler]

Here is what I would do. Use GENERATE to create a new calculated table. You can then link that table to your calendar table.

Table =    
SELECTCOLUMNS(            
	FILTER(                
		GENERATE( 
			'YourTable',
			'CalendarTable'
		),
		[Date] >= [from_date] &&
		[Date] < [billed_upto] 
	),
	"ID", [member_id],
	"Service", [service_name],
	"Amount",[amount],
	"Date", [Date]
) 

Something like that. At that point the problem should become trivial. Now the above assumes daily billing I suppose and you probably need monthly billing but that is the technique that I would use. You might just need a "calendar" table that was a list of months or month/years. If you could post some sample data in a format that can be copied and pasted easily, might be able to come up with a more specific solution.

[tachydidaxy]

@Greg_Deckler, I can't thank you enough; your pattern worked really well once I was able to get it to fit my data. Here's the measure I made from your pattern:

Total Amount =
VAR tmpCalendar = 'Calendar'
VAR tmpDues = 'Dues'
VAR tmpTable =
    SELECTCOLUMNS (
        FILTER (
            GENERATE (
                tmpDues,
                SUMMARIZE (
                    tmpCalendar,
                    'Calendar'[YearNbr],
                    'Calendar'[MonthNbr],
                    'Calendar'[YearMonth]
                )
            ),
            'Calendar'[YearMonth] >= 'Dues'[YearMonthBegin]
                && 'Calendar'[YearMonth] <= 'Dues'[YearMonthEnd]
        ),
        "Member", 'Dues'[member_id],
        "Year", 'Calendar'[YearNbr],
        "Month", 'Calendar'[MonthNbr],
        "Amount", 'Dues'[amount]
    )
RETURN
    SUMX ( tmpTable, [Amount] )

I'll need to get that book you linked to better understand why some of this is done though. In my case, since I already had the columns you added with ADDCOLUMNS in both my Calendar and Dues tables, I simply passed both tables as-is to the VAR assignments.

I'll play around with this measure this weekend and try and get it to respect the frequency needed (i.e. quarterly, annually, monthly).

[Greg_Deckler]

To change the frequency, it's all going to come down to your SUMMARIZE statement as well as your FILTER statement to some degree. So, currently you measure summarizes by Year and Month and YearMonth is there for the filtering but doesn't really affect the SUMMARIZE. So, for annual, you would only need to summarize by Year and shouldn't need anything else. For Quarter, you would replace Month with Quarter in your SUMMARIZE and have a YearQuarter.

So, what I would do would be to create some VAR's for tmp tables athat are your different summarizations of Calendar. Then you could check something that tells you what to use, grabe the correct able and pass it in as the second table for the generate. Would have to do something similar for the FILTER. Should be doable but probably a little tedious.

Now that I'm looking at it, it just might be 3 completely separate tmpTable calculations (tmpTableMonth, tmpTableYear, tmpTableQuarter) with a switch statement at the end to choose one to sum and return.

[tachydidaxy]

@Greg_Deckler

I was able to get this to respect the periods as needed using your suggestions; for instance here's the monthly calculation I came up with:

Total Temp Monthly =
VAR tmpDues =
    FILTER ( 'Dues', 'Dues'[bill_frequency_name] = "Monthly" )
VAR tmpTable =
    SELECTCOLUMNS (
        FILTER (
            GENERATE (
                tmpDues,
                SUMMARIZE (
                    'Calendar',
                    'Calendar'[YearNbr],
                    'Calendar'[MonthNbr],
                    'Calendar'[YearMonth]
                )
            ),
            'Calendar'[YearMonth] >= 'Dues'[YearMonthBegin]
                && (
                    'Calendar'[YearMonth] <= 'Dues'[YearMonthEnd]
                        || ISBLANK ( 'Dues'[YearMonthEnd] )
                )
        ),
        "Member", 'Dues'[member_id],
        "Year", 'Calendar'[YearNbr],
        "Month", 'Calendar'[MonthNbr],
        "Amount", 'Dues'[amount]
    )
RETURN
    SUMX ( tmpTable, [Amount] )

And here's the quarterly (our quarterly billing is done Feb, May, Aug, Nov 😞

Total Temp Quarterly =
VAR tmpDues =
    FILTER ( 'Dues', 'Dues'[bill_frequency_name] = "Quarterly" )
VAR tmpTable =
    SELECTCOLUMNS (
        FILTER (
            GENERATE (
                tmpDues,
                SUMMARIZE (
                    'Calendar',
                    'Calendar'[YearNbr],
                    'Calendar'[MonthNbr],
                    'Calendar'[YearMonth]
                )
            ),
            'Calendar'[YearMonth] >= 'Dues'[YearMonthBegin]
                && (
                    'Calendar'[YearMonth] <= 'Dues'[YearMonthEnd]
                        || ISBLANK ( 'Dues'[YearMonthEnd] )
                            && ( 'Calendar'[MonthNbr] = 2
                            || 'Calendar'[MonthNbr] = 5
                            || 'Calendar'[MonthNbr] = 8
                            || 'Calendar'[MonthNbr] = 11 )
                )
        ),
        "Member", 'Dues'[member_id],
        "Year", 'Calendar'[YearNbr],
        "Month", 'Calendar'[MonthNbr],
        "Amount", 'Dues'[amount]
    )
RETURN
    SUMX ( tmpTable, [Amount] )

I do have a quick question though. Your original pattern had us creating a VAR calendar table which added columns for the YEARMONTH, etc. as needed and was then used in the GENERATE() statement with the tmpDues table. I removed the VAR declaration for the Calendar as I already had all I needed in my calendar table and I'm simply passing along the Calendar table in to the calculate expression. 

My question is, when declaring the calendar VAR, is that helping in any way with resource management and/or responsiveness of the report itself?

My thinking is that when using the actual Calendar table instead of a VAR, perhaps I'm always pulling in the ENTIRE Calendar table (1900 - 2018) for use with the GENERATE() function against the tmpDues table each time the measure is calculated (which would be for each point on a visual for instance right?).

Meanwhile, if we initialized a VAR tmpCalendar instead, would that only pull in the rows from the Calendar table that were valid for the given coordinates of the visual's filter context?

Is that making sense? Basically, am I pulling in the entire Calendar table to join with the tmpDues table each time now? And does using a VAR work around that issue by only pulling the Calendar table values limited to the context of the location in the given visual?

I think I know that VARs behave really more like constants, but I also think I know they can be used in place of EARLIER() for iterating through row context and comparing current rows against another value.

[Greg_Deckler · Accepted solution]

@tachydidaxy- Thanks to @Phil_Seamark's new book, Beginning DAX with Power BI: The SQL Pro’s Guide to Better Business Intelligence, I've been able to come up with a general solution to this type of problem. Take a look at the Quick Measure "Periodic Billing" that I posted to the Quick Measures Gallery: https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365#M148

I'm pretty sure it will do what you want.

[tachydidaxy]

This looks amazing @Greg_Deckler; really do appreciate your time and help.

I'll have a chance to use this and try and work something out specifc to my report tomorrow. I may post back with some sample scrubbed data depending on how much I struggle (still really new to DAX).

Thank you again.