Converting SQL query to DAX

[Anonymous]

Scenario ::: I want to convert the SQL below to DAX , as you can see in SQL I am using ROW_Number windows function to partition by ssn and eomdate…to get the CostCenter associated with a SSN based on the month selection …. As the table shows below CostCenterID can change for a SSN from one month to the other . For example SSN=2836 had costcenterID=9 by end of march but for all previous months the Costcenterid was 23. So for me to correctly calculate the Total Members  for a CostCenter for a given date range.......

The input data looks like this::::

SSN		CostCenterID		EOMDate
44694		9		3/31/2021
44694		9		2/28/2021
44694		9		1/31/2021
28369		9		3/31/2021
28369		23		2/28/2021
28369		23		1/31/2021
33423		15		3/31/2021
33423		15		2/28/2021
33423		15		1/31/2021
13423		15		3/31/2021
13423		15		2/28/2021
13423		15		1/31/2021
18456		23		2/28/2021
18456		23		1/31/2021
12452		23		3/31/2020
12452		23		2/28/2021
12452		23		1/31/2021
46572		15		3/31/2021
46572		20		2/28/2021
46572		20		1/31/2021
46571		20		3/31/2021
46571		20		2/28/2021
46571		20		1/31/2021
46533		20		2/28/2021
46533		20		1/31/2021
56811		20		3/31/2021
56811		23		2/28/2021
56811		23		1/31/2021

Here is the SQL query I want converted to DAX based on date selected by the user via the date slicer in the PowerBI report

Select    z. SSN
            , z.CostCenterID
INTO #T_CostCenterLatest
From (
           SELECT
                                  SSN
                               , CostCenterID
                               , RowNumber = ROW_NUMBER() OVER (PARTITION BY SSN ORDER BY EOMDate DESC)
            FROM [TransArchive].dbo.AGGR_Transaction_ChannelUtilization
             WHERE EOMDate BETWEEN @dteEOMBegin AND @dteEOMEnd
)z
Where z.RowNumber=1 --Cost center of most recent record in dates selected

--Get results for the selected date
SELECT
                        CostCenterID=t.CostCenterID
                      , t.EOMDate
                      , TotalMembers=COUNT(DISTINCT(t.SSN))
FROM              [TransArchive].dbo.AGGR_Transaction_ChannelUtilization t
INNER JOIN    #T_CostCenterLatest tc ON t.SSN = tc.SSN
LEFT JOIN       dbo.COST_CENTER c ON tc.CostCenterID = c.COST_CENTER_ID
GROUP BY tc.CostCenterID,c.COST_CENTER_NBR, t.EOMDate
order by EOMDate,CostCenterID

Expected result ::: Here both the Date Range and CostCenterID values will be PowerBI report driven . I need to get the DAX for Total Members....Here I am showing the expected results based on the two Date Ranges the user selects and how that affects the Total Members count...

CostcenterID		Date Range		Total Members
9		1/1/2021 - 3/31/2021		2
15		1/1/2021 - 3/31/2021		3
20		1/1/2021 - 3/31/2021		2
23		1/1/2021 - 3/31/2021		1
CostcenterID		Date Range		Total Members
9		1/1/2021 - 2/28/2021		1
15		1/1/2021 - 2/28/2022		2
20		1/1/2021 - 2/28/2023		3
23		1/1/2021 - 2/28/2024		4

Hopefully this makes sense.

Thanks in advance

[lbendlin]

You don't really need DAX for that.  Take your data as is, and put it into a matrix visual.  Set the value aggregation to Count Distinct of SSN.  Done.