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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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.

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.