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
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |