Forum Discussion
TOTALMTD() now showing nulls or missing
I'm looking to include people who don't have hours for the prior month as blank or 0. Currently the measure looks like:
MTD Hrs = TOTALMTD(sum(Table[Hours]),[Date])
Data Example if today is 11/14:
| Employee ID | Month | Hours |
| 432 | January | 4 |
| 432 | September | 3 |
| 432 | October | 2 |
| 567 | January | 8 |
| 567 | September | 9 |
| 987 | February | 10 |
| 987 | March | 11 |
| 987 | April | 12 |
The list then shows:
| Employee ID | Hours |
| 432 | 2 |
However I'd like it to show:
| Employee ID | |
| 432 | 2 |
| 567 | |
| 987 |
Its ok if 567 and 987 show 0s instead if blank, either works
Looks like this issue is the result of a bigger issue due to not having a single date table. Thanks all for your assistance though!
3 Replies
- Ashish_Mathur
Super User
Hi,
Try this
=IF(ISBLANK(TOTALMTD(sum(Table[Hours]),[Date])),0,TOTALMTD(sum(Table[Hours]),[Date]))
Hope this helps.
v-ljerr-msftMicrosoft Employee
Hi teresadanna,
In addition, you should also be able to simply add "+ 0" to your formula to get the expected result. :smileyhappy:
MTD Hrs = TOTALMTD ( SUM ( Table[Hours] ), [Date] ) + 0
Regards
Looks like this issue is the result of a bigger issue due to not having a single date table. Thanks all for your assistance though!
