Forum Discussion

gjhannam23's avatar
gjhannam23
Frequent Visitor
6 years ago
Solved

Periodic Billing Formula Error for Monthly Billings

Hi all,

 

Could anyone tell me where I am going wrong in this formula?

 

 

Monthly Recurring Amount = 
VAR tmpCalendar = ADDCOLUMNS(
HeaderDocDate,"Month",MONTH([Date].[Date]),
"MonthYear",VALUE(YEAR([Date].[Date]) & FORMAT(MONTH([Date].[Date]),"0#"))
)
VAR tmpBilling = ADDCOLUMNS(
FILTER(DocumentItems,DocumentItems[Monthly Rec]=1),
"MonthYearBegin",VALUE(YEAR([RecurringStartDate]) & FORMAT(MONTH([RecurringStartDate]),"0#")),
"MonthYearEnd",VALUE(YEAR([RecurringEndDate]) & FORMAT(MONTH([RecurringEndDate]),"0#")))

VAR tmpTable = 
SELECTCOLUMNS(
    FILTER(
        GENERATE(
            tmpBilling,
            SUMMARIZE(tmpCalendar,[Year],[Month],[MonthYear])
        ),
        1=1
        //IF([MonthYearBegin]="",0,[MonthYear] >= [MonthYearBegin])
        //&&
        //[MonthYear] <= [MonthYearEnd]
    ),
    "Customer",'DocumentItems'[DocID],
    "Year",[Year],
    "Month",[Month],
    "Amount",[MonthlyRecurringProfit]
)
RETURN SUMX(tmpTable,[Amount])

 

 

 

I want to use Greg_Deckler 's periodic billing solution, however my tables have 3 types of recurring billing cycles - hence why I use a filter for the DocItems table. 

I keep getting a conversion error of: cannot compare text to number when I uncomment the below:

//IF([MonthYearBegin]="",0,[MonthYear] >= [MonthYearBegin])
//&&
//[MonthYear] <= [MonthYearEnd]

I've tried adding If statements to handle it but still doesnt work.

The data types all seem correct given the use of the Value formula so a bit stuck

Thanks,
 
Greg

1 Reply