Forum Discussion
Anonymous
6 years agoNot applicable
Date filter not working
Hi, I have a problem creating a measure on a report. The report is a long table in which I represent some warehouse values day by day (see the screenshot). I'm trying to insert into a measure...
AntrikshSharma
6 years agoCommunity Champion
First of all you need to understand that you are using 2 column in a boolean operation.
'F - Inventory'[Created Date Time] in 'F - Inventory'[Created Date Time]<='D - Date'[Date] expands to
FILTER ( ALL ( 'F - Inventory'[Created Date Time] ), SomeCondition )Now you are trying to get dates that are in another table, that's not allowed because FILTER & ALL contruct only has one column. and the same is true for:
'F - Inventory'[Inventory date]="1900-01-01" || 'F - Inventory'[Inventory date]>='D - Date'[Date], Are you trying to calculate a rolling total? if yes, you could use:
NEW In Acq Vib Spe =
VAR LastAvailableDate = MAX ( 'D - Date'[Date] )
VAR Result =
CALCULATE (
SUMX ( 'F - Inventory', 'F - Inventory'[Quantity] * 'F - Inventory'[Unit Cost] ),
'F - Inventory'[Transaction type] = "Ordine fornitore",
'D - Warehouse'[Warehouse ID] = "B0",
'D - Item'[Type] = "Item",
'D - Item'[SpecialUnificato] = "Speciale",
'F - Inventory'[Inventory date] = "1900-01-01"
|| 'F - Inventory'[Inventory date] >= LastAvailableDate,
'F - Inventory'[Status ID]
IN { "StatusReceipt-3", "StatusReceipt-4", "StatusReceipt-5" },
FILTER ( ALL ( 'D - Date'[Date] ), 'D - Date'[Date] <= LastAvailableDate )
)
RETURN
Result