Forum Discussion

mmilegal's avatar
mmilegal
Frequent Visitor
8 years ago
Solved

Work In Progress report from time recording data

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...
Need Help
  • Eric_Zhang's avatar
    Eric_Zhang
    8 years ago

    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.