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.
mmilegal
8 years agoFrequent Visitor
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_Mathur8 years agoSuper User
You are most welcome. Thank you for your kind words.