Forum Discussion
Handling Billing Period for Prior Month
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.
- jerime4 years agoFrequent Visitor
Any ideas or help? Ashish_Mathur amitchandak