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
|
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.
Hi @MojoGene ,
Looking at your measure believe that this should be working since you are picking up all the values between 2010 and the month end of the prior month so if you select February, and nothing is billed even if the information is based on the previous month that is january this should pickup the date that you need.
However you are only getting the current filter on the calendar table is there any other context (filters, slicers, columns in tables) that you are using to make your visualization?
Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.
If the information is sensitive please share it trough private message.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
117 | |
59 | |
59 | |
44 | |
41 |
User | Count |
---|---|
116 | |
66 | |
65 | |
64 | |
48 |