Work In Progress report from time recording data

[mmilegal]

Hello to the community

Could I please ask for your help?

I would like to use time-recording data combined with billing date data to create a Work-In-Progress or unbilled hours report.  I have found many tutorials on working with cumulatives but have failed to find a way to use them in my own implementation.  I'm sure the answer is out there but the search terms I've been using have failed to unearth it so it may be clearest if I show very basic sample data and the output I hope to generate from that.  

Example of the table imported from my source:

Job Number	Action date	Action Duration	Billing Date
1234	01 January 2017	1	10 April 2017
1234	10 January 2017	4	10 April 2017
1234	12 February 2017	9	10 April 2017
1234	12 March 2017	7	10 April 2017
1234	02 April 2017	2	10 April 2017
4567	07 January 2017	8	02 July 2017
4567	10 March 2017	7	02 July 2017
4567	28 April 2017	4	02 July 2017
4567	10 June 2017	5	02 July 2017
6789	21 March 2017	12	26 April 2017
6789	01 April 2017	3	26 April 2017
6789	08 April 2017	5	26 April 2017
6789	12 April 2017	4	26 April 2017

Format of output I hope to achieve:

Job Number	Jan	Feb	Mar	Apr	May	June
1234	5	14	21	0	0	0
4567	8	8	15	19	19	24
6789	0	0	12	0	0	0

So, job 1234 had 5 hours worked in Jan that were unbilled at the end of Jan, so 5 hours was unbilled at that point.  9 hours were added in Feb and 7 in March resulting in 14 and 21 hours 'in-progress' at those month ends respectively.  Although 2 hours were added in April, the work was billed in full within that month resulting in a zero balance at the end of April.  

In my scenario, not all jobs will have a billing date.  The absence of a billing date should be interpreted as a future billing date i.e. unbilled hours will accrue or carry forward until a billing date is entered.  My scenario does not need to handle partial billings or write-offs i.e. a billing date will negate all unbilled hours to date and result in a period end aggregate of zero.  

Apologies in advance for the length of the post and the almost inevitable duplication of the question.  I know my first post comes only minutes after registering but I have searched this forum and the wider web for a couple of (fruitless) days - I promise!

Thank you in advance for any help you can offer.  

Kind regards

GF

[mmilegal]

@Eric_Zhang @Ashish_Mathur @fhill

Many thanks to you all for your helpful suggestions.  The fact you each provided slightly different solutions is tremendously helpful.  Some time spend deconstructing your suggestions combined with revisiting msdn's help pages on DAX is so much more helpful than just the msdn pages alone.  Seeing how the commands can be used in a familiar scenario is like accelerated learning!

I now not only have a solution to my immediate problem but am better equipped to deal with the next one!  Your generosity with your time and knowledge is greatly appreciated.  

[Ashish_Mathur]

You are most welcome.  Thank you for your kind words.

[Ashish_Mathur]

Hi @mmilegal,

You may refer to my solution here.

Hope this helps.

[Eric_Zhang · Accepted solution]

@mmilegal

You can create a calendar table 

calendar = CALENDAR(MIN(yourTable[Action date]),MAX(yourTable[Action date]))

Month = EOMONTH('calendar'[Date],0)

Then create a measure as below.

unBilledHoursTotal =
VAR unBilledHoursInTotal =
    SUMX (
        FILTER (
            FILTER (
                ALLSELECTED ( yourTable ),
                yourTable[Job Number] = MAX ( yourTable[Job Number] )
            ),
            yourTable[Action date] <= MAX ( 'calendar'[Month] )
        ),
        yourTable[Action Duration]
    )
VAR isCurrentMonthBillMonth =
    DATE ( YEAR ( MAX ( yourTable[Billing Date] ) ), MONTH ( MAX ( yourTable[Billing Date] ) ), 1 )
        = DATE ( YEAR ( MAX ( 'calendar'[Month] ) ), MONTH ( MAX ( 'calendar'[Month] ) ), 1 )
VAR billDate =
    IF (
        ISBLANK ( MAX ( yourTable[Billing Date] ) ),
        DATE ( 2099, 1, 1 ),
        MAX ( yourTable[Billing Date] )
    )
RETURN
    SWITCH (
        TRUE (),
        ISBLANK ( unBilledHoursInTotal ), 0,
        isCurrentMonthBillMonth, 0,
        MAX ( 'calendar'[Month] ) >= billDate, 0,
        unBilledHoursInTotal
    )

See more details in the attached pbix file.