I am trying to get the equivalent logic in DAX for below SQLdeclare @date datetimedeclare @warehouse nvarchar(225)declare @province nvarchar(225)set @Transdate = '2021-09-18 00:00:00.000'set @province = 'BC'set @warehouse = 'BC-01' ----- 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 rnFROM [EDW].[FACT].[InventoryBalancedet_2] dtWHERE province = @provinceand warehouseid = @warehouseand transdate <= @Transdate),------ THIS CTE RANKS amount of an item by warehouse -----------main2 as (select sum(Amount) as AMOUNT, ItemID,warehouseidFROM [EDW].[FACT].[InventoryBalancedet_2]WHERE province = @provinceand warehouseid = @warehouseand transdate <= @Transdategroup by ItemID , warehouseid),------------ COMBINING THE QUERIES TOGETHER --------------------main3 as (SELECT m2.AMOUNT, m2.warehouseid , m1.CategoryFROMmain1 m1inner join main2 m2 on m1.warehouseid = m2.warehouseidand m1.ItemID = m2.ItemIDand m1.rn = 1)select sum(m3.total) ,m3.CategoryFROM main3 m3group by m3.Category

Happy New Year, pal! Thanks for tagging me for some tricky game of DAX. I'm afraid you were mislead by PO's sql; first of all, I simplied it this way, 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' ; with main1 as ( SELECT * , ROW_NUMBER() OVER(Partition by itemid ORDER BY transdate DESC) AS [rn] , SUM([Amount]) OVER (PARTITION BY ItemID) AS [Total] FROM [dbo].[MATDW SAMPLE DATA] dt WHERE transdate <= @Transdate and province = @province and warehouseid = @warehouse) select * from main1 where [rn]=1 DAX Total = VAR __cat = MAX( 'MATDW SAMPLE DATA'[Category] ) VAR __summary = CALCULATETABLE( ADDCOLUMNS( ADDCOLUMNS( SUMMARIZE( 'MATDW SAMPLE DATA', 'MATDW SAMPLE DATA'[ITEMID], 'MATDW SAMPLE DATA'[WarehouseID] ), "@amt", CALCULATE( SUM( 'MATDW SAMPLE DATA'[Amount] ) ), "@maxdate", CALCULATE( MAX( 'MATDW SAMPLE DATA'[TRANSDATE] ) ) ), "@Cat", VAR __dt = [@maxdate] RETURN CALCULATE( MAX( 'MATDW SAMPLE DATA'[Category] ), 'MATDW SAMPLE DATA'[TRANSDATE] = __dt ) ), ALLSELECTED( 'MATDW SAMPLE DATA'[Category] ) ) RETURN IF( __cat IN SELECTCOLUMNS( __summary, "_cat", [@Cat] ), SUMX( FILTER( __summary, [@Cat] = __cat ), [@amt] ) )

SQL to DAX conversion | Microsoft Fabric Community

Forum Discussion

Related content

SQL to DAX conversion

Conversion of Excel formulas to DAX within Power BI.

FX Conversion Logic

SQL to DAX Conversion

SQL to DAX Conversion