SQL to DAX conversion

amikm  I deleted my earlier answer cause I realized it was wrong.

I spent some time with this but could not make it to work, looping parry2k AlexisOlson CNENFRNL 

parry2k AlexisOlson CNENFRNL  OP has a TSQL query like following

declare @Transdate datetime
declare @warehouse nvarchar(225)
declare @province nvarchar(225)

set @Transdate = '2016-07-11 00:00:00.000'
set @province = 'BC'
set @warehouse = 'BC-52'



----- THIS CTE  RANKS THE ITEMS TO GET THE MOST RECENT category -----------
;with main1 as (
SELECT  * ,
ROW_NUMBER() OVER(Partition by itemid, warehouseid   ORDER BY transdate DESC) AS rn    
FROM [newtest].[dbo].[MATDW SAMPLE DATA] dt
WHERE  transdate <=  @Transdate
and province = @province
and warehouseid = @warehouse

)
,main2 as (
select sum(Amount) as AMOUNT
, ItemID
,warehouseid

FROM [newtest].[dbo].[MATDW SAMPLE DATA]
WHERE  transdate <=  @Transdate
and province = @province
and warehouseid = @warehouse

group by ItemID , warehouseid  
)

,main3 as (
SELECT m2.AMOUNT, m2.warehouseid , m1.Category
FROM
main1 m1
inner join main2 m2 on m1.warehouseid = m2.warehouseid
and m1.ItemID = m2.ItemID
and m1.rn = 1
)
,main4 as (select sum(m3.AMOUNT)  AS AMOUNT ,m3.Category
FROM main3 m3
group by  m3.Category )

select * from main3 /*where rn=1*/

--select *
--FROM [newtest].[dbo].[MATDW SAMPLE DATA]
--WHERE  transdate <=  @Transdate
--and province = @province
--and warehouseid = @warehouse and ITEMID=10000

which produces this

The equivalent DAX query for a dervied table would be following

Table = 
VAR _basicFilterDate =
    DATE ( 2016, 7, 11 )
VAR _basicFilterProvince = "BC"
VAR _basicFilterWarehouse = "BC-52"
VAR _filteredTable =
    FILTER (
        'MATDW SAMPLE DATA',
        'MATDW SAMPLE DATA'[Province] = _basicFilterProvince
            && 'MATDW SAMPLE DATA'[TRANSDATE] <= _basicFilterDate
            && 'MATDW SAMPLE DATA'[WarehouseID] = _basicFilterWarehouse
    )
var _sum = ADDCOLUMNS(_filteredTable,"sum",SUMX(FILTER(_filteredTable,[ITEMID]=EARLIER([ITEMID])&&[WarehouseID]=EARLIER([WarehouseID])),[Amount]))
var _date = ADDCOLUMNS(_sum,"maxDT",MAXX(FILTER(_sum,[ITEMID]=EARLIER([ITEMID])&&[WarehouseID]=EARLIER([WarehouseID])),[TRANSDATE]))
var _filter = FILTER(_date,[TRANSDATE]=[maxDT])
var _groupBY = SUMMARIZE(_filter,[Category],[sum])
var _aggregation = GROUPBY(_groupBY,[Category],"_sum",SUMX(CURRENTGROUP(),[sum]))
RETURN _aggregation

which returns this

However, how can this be converted to measure that reponses to a slicer

VAR _basicFilterDate =
    DATE ( 2016, 7, 11 )
VAR _basicFilterProvince = "BC"
VAR _basicFilterWarehouse = "BC-52"

Created a pbix for your convenience as it has bothered me for a while.