I don’t blame you for not being able to forgive him for not apologising Op, especially if this is a pattern of behaviour on his part, and not a one off; your dh was completely in the wrong. Your house as you describe it in your update doesn’t even sound that messy to me.
First, he should not be shouting at anyone full stop. If he had a problem he needs to express it in a reasonable manner, at an appropriate time, to the correct person.
In this instance he should have waited until he could talk to you in private and quietly expressed his irritation at the mess. That would have given you a chance the next morning to ask your nieces to give you a hand tidying up.
It was really rude to address your nieces directly as they are guests. And he didn’t know that you had hurried them out of the house bc he launched in to shouting without hearing your side of the story.
I suppose it might be mildly irritating to come home from work and find everyone is out having fun, the cleaner hasn’t come and the house is a mess, but that’s life. It’s the summer holidays fhs! He has young boys! It’s normal for the house to get messy when the family gathers together and it’s no big deal.
My dh would have maybe stacked the dw, Collected up any rubbish and the worst of the toys, emptied the bins, ordered a takeaway and sat down in front of the tv surrounded by the forts and enjoyed his child free time! And he would be genuinely pleased we were having a good time too!
Keeping a house clean and tidy is never more important imho than maintaining good family relationships and making guests feel at home, especially young relations. Yes they should have been a bit tidier but lots of people go through a messy stage in their twenties and it wasn’t your dh’s place to intervene.
The fact that his reaction was so extreme and he can’t or won’t see where he went wrong, makes me worried for your boys when they become teens tbh Op.
If you are permanently walking on eggshells around him, you may need to reconsider this relationship. It’s no fun living with someone who can’t regulate their emotions, who takes their frustrations out on everyone else, who is never wrong, who can’t apologise, who doesn’t respect your side of the family, who can’t take pleasure in other people’s happiness, and who frankly sounds like a bully.