Hello all, I am having some issues with trying to get the Periodic Billing Dax Formula to work i have contracts that can be 1,2 or 3 year contract with Revenue recognized monthly with the first and last months being prorated.
I get the
and i am not 100% why i verified each fields to make sure it wasnt a text field. any help would be appreciated. Thank you
Total Amount =
VAR tmpCalendar = ADDCOLUMNS('DateDim',"Month",MONTH('DateDim'[FullDate]),"Year",YEAR('DateDim'[FullDate]),"MonthYear",VALUE(YEAR(DateDim[FullDate]) & FORMAT(MONTH(DateDim[FullDate]),"0#")))
VAR tmpBilling = ADDCOLUMNS(BillingContractItem,"MonthYearBegin",VALUE(YEAR('BillingContractItem'[RevenueStartDate]) & FORMAT(MONTH(BillingContractItem[RevenueStartDate]),"0#")),
"MonthYearEnd",VALUE(YEAR(BillingContractItem[ContractEndDate]) & FORMAT(MONTH(BillingContractItem[ContractEndDate]),"0#")))
VAR tmpTable =
SELECTCOLUMNS(
FILTER(
GENERATE(
tmpBilling,
SUMMARIZE(tmpCalendar,[Year],[Month],[MonthYear])
),
[MonthYear] >= [MonthYearBegin] &&
[MonthYear] <= [MonthYearEnd]
),
"Customer",BillingContractItem[Name],
"Year",[Year],
"Month",[Month],
"Amount",BillingContractItem[TotalContractItemValue]
)
RETURN SUMX(tmpTable,[Amount])
