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

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

  • 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.