Create New Table

[Anonymous]

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.

[v-zhangti]

Hi, @Anonymous 

Can you provide sample data for testing? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures or Excel. I look forward to your response.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.