There are two tables : New_Errors and New_Payments.
New Errors New Payments
Accn_Id Accn_Id
Proc_code Proc_code
Fixed_Date Pmt_Date
payors_id
I want to join two tables on keys Accn_Id and Proc_code and The new tables should have the coumns Accn_Id, Proc_code and their respective Fixed_Date, Pmt_Date and payors_id. Later I want to find the Avg DatedDiff (Fixed_Date, Pmt_Date). Can someone please help me write a Dax query to create the new table? I have used the below SQL Query to do the same in SQL.
WITH CTE AS (
SELECT P.[accn_id], E.[proc_code], MAX(P.[pmt_date]) AS PmtDate, MAX(E.[fix_date]) as FixDate
FROM [dbo].[New_Errors] AS E
JOIN [dbo].[New_Payments] AS P ON E.[accn_id] = P.[accn_id] AND E.[proc_code] = P.[proc_code]
WHERE E.[fix_date] <> 'NULL'
GROUP BY P.[accn_id], E.[proc_code]
)
SELECT [proc_code] , AVG(DATEDIFF(DAY, PmtDate, FixDate)) AS avg_date_diff
FROM CTE
GROUP BY [proc_code] ;
Thank you in advance.
