Billing Over Multiple Months Calculating Days and Amount billed per Month

[WalterBuck]

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?

[techies · Accepted solution]

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])