Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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 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?
General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

4 Replies

  • tex628's avatar
    tex628
    Community Champion
    6 years ago

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

    Br,
    J

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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.

  • AntrikshSharma's avatar
    AntrikshSharma
    Community Champion
    6 years ago

    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