March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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.
@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.
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
90 | |
89 | |
85 | |
73 | |
49 |
User | Count |
---|---|
167 | |
149 | |
93 | |
72 | |
58 |