Distinct Count on a Column in Group By

[UG14]

I have to create a table in Power BI from SQL Query-

**Select
I.CompanyID,
I.CompanyName,
ID.Service,
--Year(I.InvoiceStartDate) as Year,
--Month(I.InvoiceStartDate) as Month,
SUM(ID.TimeInvoiced) as 'SumOfTimeInvoiced',
SUM(ID.FinalServiceAmount) as 'SumOf3Months',
count(distinct Month(I.InvoiceStartDate)) as 'Count',
convert(decimal(8,2), SUM(ID.FinalServiceAmount) / count(distinct Month(I.InvoiceStartDate))) as 'AvgOf3Months'
from Invoice I
Join InvoiceDetail ID on I.InvoiceID = ID.InvoiceID
WHERE --ID.Service NOT IN ('Print Shop Postage', 'Print Shop Services', 'Implementation Fee', 'Management Fee', 'Other') AND
(cast(I.InvoiceStartDate as Date) BETWEEN DATEADD(MONTH, -5, DATEADD(DAY, 1, EOMONTH(GETDATE()))) AND DATEADD(MONTH, -3, DATEADD(DAY, 1, EOMONTH(GETDATE()))))
GROUP BY I.CompanyID, I.CompanyName, ID.Service--, YEAR(I.InvoiceStartDate), Month(I.InvoiceStartDate) **

Now I am trying to group the data to aggregate the sum of TimeInvoiced & FinalServiceAmount columns and "count distinct months of the InvoiceStartDate" Column.. But there is no option to group data and count distinct months along with the sum aggregation.. Please help and let me know how can I do it? 

I would have used Summarize function easily but the problem is I have to join this table with another table using full outer join which is not available as a DAX function..... Please Help!

[amitchandak]

@UG14 , You have to create rolling measure to deal with rest are slicer and axis/row/column/group by in power bi visual

example

Rolling 3 till last month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd(Sales[Sales Date],-1,month)),-3,MONTH))
Rolling 3 till last 2 month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd(Sales[Sales Date],-2,month)),-3,MONTH))
Rolling 3 till last 1 month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd(Sales[Sales Date],-1,month)),-3,MONTH))
Rolling 12 till last month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd(Sales[Sales Date],-1,month)),-13,MONTH))
Rolling 3 till last 3 month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd(Sales[Sales Date],-3,month)),-3,MONTH))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[V-lianl-msft]

Hi @UG14 ,

Try to create 2 measures:

Measure = CALCULATE(DISTINCTCOUNT(Table1[InvoiceStartDate].MONTH),ALLEXCEPT(Table1,Table1[TimeInvoiced ],Table1[FinalServiceAmount ]))

Measure 2 = SUMX(ALLEXCEPT(Table1,Table1[XX]),[Measure])

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.