Problem is solved by using Dense_Rank function and the blog that helped me solve problem of not being able to use COUNT DISTINCT in the OVER clause with PARTITION BY clause is http://beyondrelational.com/modules/2/blogs/51/posts/15524/distinct-counts-using-aggregate-functions-with-over-clause.aspx.

SELECT Yr

,Mo

, Invoice

,D_Type

,Status

,MAX(RankByInvoice) over (partition by Yr, Mo) as NumDistinctInvoices

,RemediationDate

FROM

(

SELECT

Invoice,

RemediationDate,

D_Type, Status,

YEAR(RemediationDate) as Yr,

LEFT(DATENAME(mm,RemediationDate),3) as Mo,

RankByInvoice = DENSE_RANK() OVER (PARTITION BY YEAR(RemediationDate), LEFT(DATENAME(mm,RemediationDate),3) ORDER BY Invoice)

FROM dbo.Remediation

)p

Another link for solving COUNT DISTINCT with OVER clause is here

http://stackoverflow.com/questions/13480880/window-functions-to-count-distinct-records.

This was a tricky problem for me to solve and I didn't give it a good title or explain the end use of my query which had become a Grouping Set problem after Partition by wasn't working. It would have helped, I think, if I'd explained background and also destination (Excel) or result set!

I do not need to use GROUPING SETS and can go back to using Windowing functions for getting Count Distinct. Thanks to Jeff and Matak for feedback.