Forum Discussion

jerime's avatar
jerime
Frequent Visitor
4 years ago

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 prob...
Need Help
jerime's avatar
jerime
Frequent Visitor
4 years ago

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's avatar
Ashish_Mathur
Icon for Super User rankSuper User
4 years ago

Hi,

Share some data and show the expected result.

  • jerime's avatar
    jerime
    Frequent Visitor
    4 years ago

    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.