Are you doing your analysis is Excel or PowerBI? (You can use DAX for that... I'd check on Ferrari & Russo's website(s) for answers on DAX... I know there's one on handling different granularities on http://www.daxpatterns.com.

But without a lot more information, it's hard to tell.

Are you trying to do this purely in T-SQL?  What's your presentation layer - Excel, PowerBI, SSRS? Something else?

Here's the link to the F& R's DAXPatterns page...