The company in question provides professional services. At any given time, there are many ongoing projects for many clients. Each project has a certain billing frequency that may be Monthly, Quarterly or Bi-Monthly. When projects are due for billing, all unbilled transactions (Work in Process) on the projects are billed through the close of the billing cycle, which is the last day before the job is due for billing. So, for example, if a job is billed quarterly starting in January all unbilled transactions through the immediately preceding December 31st are included in the January bill.
In any given month, many projects with different billing frequencies will be due for billing. For example, January billing will include all projects billed on a Monthly basis, all projects billed on a quarterly basis starting in January (Jan/Apr/Jul/Oct), and all projects billed Bi-Monthly starting in January (Jan/Mar/May/Jul/Sept/Nov). I created a table called "Bill Freq Map" to correlate the billing frequency with a month name to assist in identifying all projects that are due for billing in any given month:
Index BillFreq Month Name | 1 | B1 (Starting Jan) | January | | 2 | B1 (Starting Jan) | March | | 3 | B1 (Starting Jan) | May | | 4 | B1 (Starting Jan) | July | | 5 | B1 (Starting Jan) | September | | 6 | B1 (Starting Jan) | November | | 7 | B2 (Starting Feb) | February | | 8 | B2 (Starting Feb) | April | | 9 | B2 (Starting Feb) | June | | 10 | B2 (Starting Feb) | August | | 11 | B2 (Starting Feb) | October | | 12 | B2 (Starting Feb) | December | | 13 | Q1 (Jan, Apr, Jul, Oct) | January | | 14 | Q1 (Jan, Apr, Jul, Oct) | April | | 15 | Q1 (Jan, Apr, Jul, Oct) | July | | 16 | Q1 (Jan, Apr, Jul, Oct) | October | | 17 | Q2 (Feb, May, Aug, Nov) | February | | 18 | Q2 (Feb, May, Aug, Nov) | May | | 19 | Q2 (Feb, May, Aug, Nov) | August | | 20 | Q2 (Feb, May, Aug, Nov) | November | | 21 | Q3 (Mar, Jun, Sep, Dec) | March | | 22 | Q3 (Mar, Jun, Sep, Dec) | June | | 23 | Q3 (Mar, Jun, Sep, Dec) | September | | 24 | Q3 (Mar, Jun, Sep, Dec) | December | | 25 | Monthly | January | | 26 | Monthly | February | | 27 | Monthly | March | | 28 | Monthly | April | | 29 | Monthly | May | | 30 | Monthly | June | | 31 | Monthly | July | | 32 | Monthly | August | | 33 | Monthly | September | | 34 | Monthly | October | | 35 | Monthly | November | | 36 | Monthly | December |
|
Thus, by selecting the Month Name, all projects due for billing in the selected month will show.
Where I am encountering difficulty is in filtering the transactions due for billing so as to include only those transactions through the end of the billing cycle. I started with what I now understand is an overly simplistic approach. I calculated the value of the unbilled transactions (WIP) to the End Of Prior Month with this measure:
WIP Value EOPM =
CALCULATE (
SUM ( Transactions[Amt] ),
Transactions[Stage] <> "Billed",
DATESBETWEEN (
'Date Table'[Date],
DATE ( 2010, 1, 1 ),
MAX ( 'Date Table'[Last Day of Prior Month] )
)
)
"Last Day of Prior Month" references this column in the Date Table:
Last Day of Prior Month = EOMONTH(TODAY(),-1)
This measure actually works exactly the way I want, but only if the measure is run in the month the project is due to be billed. So, if I want to see the amounts due to be billed in January AND the measure is executed IN January, the measure works fine. What often happens, however, is that some bills get run late. A project due for January billing might not actually get billed until the first week of February. Here is where the measure breaks down because now the filter ("Last Day of Prior Month") returns WIP through the end of January rather than through the last day of the billing cycle, December 31st.
Where I am stuck is I need a way to filter the Transactions table in such a way that it returns only those unbilled transactions due for billing up to the end of the closing cycle. Thanks for any assistance.
