Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jerime
Frequent Visitor

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.

5 REPLIES 5
amitchandak
Super User
Super User

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

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? 

Hi,

Share some data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

jerime_0-1631886782407.png

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_periodPERIOD DATEaccount_numberaccount_add_date
202109September 1, 2021123456July 1, 2021
202108August 1, 2021234567May 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
Frequent Visitor

Any ideas or help? @Ashish_Mathur @amitchandak 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.