Forum Discussion

jerime's avatar
jerime
Frequent Visitor
4 years ago

Handling Billing Period for Prior Month

I have a table that has a billing period formatted as "202109". I added a column to my table that turns that into a useable date like this, Date.From(Text.From([SI_PRD])&"01", "de-DE").

The problem I'm having is I'm trying to graph my churn into a line chart and my months are off. Accounts are added in August for example but the billing takes place in September. I'm not really sure how to handle this or if this will make any sense. I'm getting my net from comparing the billing month to the prior month in a measure and then looking at accounts added in a month in another measure.

Need Help

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    jerime , Based on what I got. Seem like you do not have a date.

     

    Create a table say Date with Year month (YYYYMM) format and add a rank column

     

    Not you can create this month and last month's columns and use that. I am using the same measure in the example you need a different one

     

    example

     

    new column

     

    Month Rank = RANKX(all('Date'),'Date'[Year Month],,ASC,Dense)

     

    measures

    This Month = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])))
    Last Month = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-1))

    • jerime's avatar
      jerime
      Frequent Visitor
      4 years ago

      Sorry, for the poor explanation. I do actually have a date column that was created from the billing period column (202109 to date) that is created in power query with this, Date.From(Text.From([SI_PRD])&"01", "de-DE").

      That power query column, we'll call it PERIOD DATE, is what I have tied to a date dimension table. From there I am using two measures.

      One to get my net added accounts from the previous billing period,

      Net Accounts - Month = 
VAR TIMEFRAME =
	CALCULATE(
		DISTINCTCOUNT('billing_table'[account_number]),
		DATEADD('Calendar'[Date], -1, MONTH)
	)
RETURN
	DISTINCTCOUNT('billing_table'[account_number]) - TIMEFRAME

       

      And then one to get my new account by date added (notice this one uses a different date relationship),

      New Agreements - Month = 
	CALCULATE(
        [Net Accounts],
		USERELATIONSHIP('Calendar'[Date], billing_table[account_add_date])
	)

       

      This works great except my months are staggared one month off because new accounts are added the month before billing happens. Does that make better sense?