Forum Discussion
mmilegal
8 years agoFrequent Visitor
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...
- 8 years ago
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.
Eric_Zhang
8 years agoMicrosoft Employee
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.