Billing Over Multiple Months Calculating Days and Amount billed per Month

WalterBuck — Wed, 30 Apr 2025 18:45:02 GMT

Hello,

I am trying to create a model for utilities billing to help accounting team do accruals. I basically have three tables.

1. Master ID list of all accounts

2. Billing per acct (see below basic setup)

3. Calendar table

So the goal is for the model to calculate the average amount billed per day based on historical data (easy enough) and calculate the number of days in accrual period that are billed / missing bills. (normally looking per month but will be accruing for multiple months at one time). I have been researching online and found a lot of great videos on events in progress but this is mostly to tell you for example how many accts were billed sometime during a month. Meaning if an acct was billed for 1 day it would count same as one that was billed for 31 days. I need to know per acct how much each acct was billed during any given month. I'd also like to be able to do the same calculation with the invoice amount so I can show the regularity of the billing over service periods (obviously that part would be an average since I don't have daily level invoice amt detail). I can't seem to figure out the right approach to getting this going. Any advice especially on getting days billed in a particular period?

Re: Billing Over Multiple Months Calculating Days and Amount billed per Month

techies — Wed, 30 Apr 2025 19:41:13 GMT

Hi WalterBuck As i understand, we can create an expanded billing table to break each billing period into daily rows and assign amount per day like this

BillingExpanded =

ADDCOLUMNS (

GENERATE (

Billing,

VAR StartDate = [Service Start]

VAR EndDate = [Service Stop]

RETURN

CALENDAR(StartDate, EndDate)

"DailyAmount",

DIVIDE([Amount billed],

DATEDIFF([Service Start], [Service Stop], DAY) + 1

)

and then measure

MonthlyAccrual =

SUM(BillingExpanded[DailyAmount])

topic Re: Billing Over Multiple Months Calculating Days and Amount billed per Month in DAX Commands and Tips

Billing Over Multiple Months Calculating Days and Amount billed per Month

Re: Billing Over Multiple Months Calculating Days and Amount billed per Month