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):   Th...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Community 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.

Anonymous's avatar
Anonymous
Not applicable
8 years ago

 

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.