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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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