Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Calculate Fees Due from Duration Range without Calendar Table Relationship

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.

Need Help

7 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    8 years ago

    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.

     

    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      8 years ago

      Anonymous- 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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      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's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        8 years ago

        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.