DAX filter the dates between two disconnected fact tables and calculate total amount

[Mohan128256]

Hello All,

I have two fact tables in my report as InventorySnapshot and Trans_Inventory which has no any relationship between the tables.

I am filtering the dates from Inventory Snapshot table and based on the date selection, will be calculating all the inventory amount from Trans inventory table which are greater than or equal to the selected date.

In above image, for selected product, date = 31-05-2023, we have three records on or after the selected date, in trans inventory table.

Below is the dax i am using for calcualte Inventory Amt.

_Inventory Amt FIN = 
VAR TotalAmt = SUM('Trans Inventory'[Inventory Amt])
VAR SelectedDate = CALCULATE(MAX('Inventory Snapshots'[Snapshot Date]),ALLSELECTED('Inventory Snapshots'[Snapshot Date]))
VAR Amt = 
CALCULATE(TotalAmt
,    FILTER(ALLSELECTED('Trans Inventory'[Physical Date]),
'Trans Inventory'[Physical Date] >= SelectedDate ) )
return 
IF(amt=0,BLANK(),amt) 

the expected result should be the total value of 1983, where as it is showing the 2169 as total value which is wrong.

Can anyone please correct my dax expression and help me out.

Thanks,

Mohan V.

[Greg_Deckler · Accepted solution]

@Mohan128256 Variables don't work that way. Variables are constants, not variable despite the name. Maybe:

_Inventory Amt FIN = 
VAR SelectedDate = MAXX(ALLSELECTED('Inventory Snapshots'[Snapshot Date]), 'Inventory Snapshots'[Snapshot Date])

VAR __Table = FILTER( FILTER(ALLSELECTED('Trans Inventory'[Physical Date]),
'Trans Inventory'[Physical Date] >= SelectedDate )

VAR Amt = SUMX( __Table, [Inventory Amt])

VAR __Result = IF( Amt = 0, BLANK(), Amt )
RETURN
  __Result