Periodic billing

[Anonymous]

Hello -

I'm trying to utilize Phil Seamark's periodic billing measure but am having trouble. Here is that link: https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

I have start and end dates in two separate columns for each contract, and a total contract $ amount.

I have a disconnected table.

I keep getting an error that says "Cannot convert value" of type Text to type Number

Anything helps 😁

[v-xuding-msft]

Hi @Anonymous ，

Based on my test, your formula works fine in my sample. I think you need to check the columns of which data type is text.

My sample

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Kudos are nice too.

[Anonymous]

Draw Schedule =
VAR tmpCalendar = ADDCOLUMNS('Disconnected Date',"Month",MONTH([Date]),"Year",YEAR([Date]),"MonthYear",VALUE(YEAR([Date]) & FORMAT(MONTH([Date]),"0#")))
VAR tmpBilling = ADDCOLUMNS('POWER BI - Gantt w NOR 191030',"MonthYearBegin",VALUE(YEAR([Est Start Date]) & FORMAT(MONTH([Est Start Date]),"0#")),
"MonthYearEnd",VALUE(YEAR([Est Comp Date]) & FORMAT(MONTH([Est Comp Date]),"0#")))
VAR tmpTable =
SELECTCOLUMNS(
FILTER(
GENERATE(
tmpBilling,
SUMMARIZE(tmpCalendar,[Year],[Month],[MonthYear])
),
[MonthYear] >= [MonthYearBegin] &&
[MonthYear] <= [MonthYearEnd]
),
"Customer",[Project ID & Description],
"Amount",[NOR]
)
RETURN SUMX(tmpTable,[Amount])

[v-xuding-msft]

Hi @Anonymous ,

Did you solve the problem? Did you check the data type of the columns? If my answer is helpful, please accept it as a solution. If you are still confused about it, please share your dummy file. Then I will understand clearly and solve it as soon as possible.

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Kudos are nice too.