Handling Billing Period for Prior Month

[jerime]

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.

[amitchandak]

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

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? 

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[jerime]

The above graph is what I'm dealing with. The Net Agreements - Month needs to be shifted to the left one month. The two measures I posted above are what create the graph (swap the word account for agreement). I use a calendar date relationship for the axis. 

A sample of my data would simply be this:

billing_period	PERIOD DATE	account_number	account_add_date
202109	September 1, 2021	123456	July 1, 2021
202108	August 1, 2021	234567	May 4, 2021

Note: PERIOD DATE is being polulated from billing period in power query with Date.From(Text.From([SI_PRD])&"01", "de-DE").

And then I have the 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])
	)

Along with a date reference table (Calendar) that has an active relationship to the PERIOD DATE and an inactive realtionship to the account_add_date.

I think that is everything. I'm not sure how much clearer I can be. It seems like what I want to do should be fairly straight forward and easy. I can't really upload any sample pbix files because I'm connnected to a live database. I suppose I could recreate one if need be. 

[jerime]

Any ideas or help? @Ashish_Mathur @amitchandak