Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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!
@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.
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.