Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
UG14
Frequent Visitor

Distinct Count on a Column in Group By

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!

2 REPLIES 2
amitchandak
Super User
Super User

@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.

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
V-lianl-msft
Community Support
Community Support

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.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!