Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MojoGene
Post Patron
Post Patron

Stuck on Time Filter

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
1B1 (Starting Jan)January
2B1 (Starting Jan)March
3B1 (Starting Jan)May
4B1 (Starting Jan)July
5B1 (Starting Jan)September
6B1 (Starting Jan)November
7B2 (Starting Feb)February
8B2 (Starting Feb)April
9B2 (Starting Feb)June
10B2 (Starting Feb)August
11B2 (Starting Feb)October
12B2 (Starting Feb)December
13Q1 (Jan, Apr, Jul, Oct)January
14Q1 (Jan, Apr, Jul, Oct)April
15Q1 (Jan, Apr, Jul, Oct)July
16Q1 (Jan, Apr, Jul, Oct)October
17Q2 (Feb, May, Aug, Nov)February
18Q2 (Feb, May, Aug, Nov)May
19Q2 (Feb, May, Aug, Nov)August
20Q2 (Feb, May, Aug, Nov)November
21Q3 (Mar, Jun, Sep, Dec)March
22Q3 (Mar, Jun, Sep, Dec)June
23Q3 (Mar, Jun, Sep, Dec)September
24Q3 (Mar, Jun, Sep, Dec)December
25MonthlyJanuary
26MonthlyFebruary
27MonthlyMarch
28MonthlyApril
29MonthlyMay
30MonthlyJune
31MonthlyJuly
32MonthlyAugust
33MonthlySeptember
34MonthlyOctober
35MonthlyNovember
36MonthlyDecember

 

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.

1 REPLY 1
MFelix
Super User
Super User

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.