Forum Discussion
gjhannam23
6 years agoFrequent Visitor
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,
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
- v-chuncz-msft
Community Support