Date filter not working

[Anonymous]

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 a date filter in which I say (for each row of the report):

F - Inventory[Created Date Time] <= (the field date on the report, that is taken from a field of dimensional table D - Date[Date]).

I tried this way:

NEW In Acq Vib Spe = 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]>='D - Date'[Date],

'F - Inventory'[Status ID] IN { "StatusReceipt-3", "StatusReceipt-4", "StatusReceipt-5"},

'F - Inventory'[Created Date Time]<='D - Date'[Date]

)

But it's not working. What is the right syntax to include in the formula?

[AntrikshSharma]

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

[amitchandak]

@Anonymous , Try like

NEW In Acq Vib Spe = CALCULATE
(
SUMX('F - Inventory','F - Inventory'[Quantity]*'F - Inventory'[Unit Cost]),
,
'D - Warehouse'[Warehouse ID]="B0",
'D - Item'[Type]="Item",
'D - Item'[SpecialUnificato]="Speciale",
Filter('F - Inventory','F - Inventory'[Transaction type]="Ordine fornitore" && ('F - Inventory'[Inventory date]="1900-01-01" || 'F - Inventory'[Inventory date]>='D - Date'[Date]) && 'F - Inventory'[Status ID] IN { "StatusReceipt-3", "StatusReceipt-4", "StatusReceipt-5"}  
&& 'F - Inventory'[Created Date Time]<='D - Date'[Date])
)

[Anonymous]

Hi, I tried your solution but the problem is that I cannot insert 'D - Date'[Date]. If I begin to write it suggests me only measures, while 'D - Date'[Date] is a field.

[tex628]

Looks like you're comparing datetype with string type. Instead of using "1900-01-01", use DATE(1900,1,1).

Br,
J