Forum Discussion

CarlsBerg999's avatar
CarlsBerg999
Helper V
4 years ago
Solved

MAX Date returns blank for some rows (Calculated Column)

Hi, 

I have a calculated column for which the goal is to search for the latest date when an item was purchased. 

The DAX is as follows:

VAR ProductionDate = 'In-House Production Costs'[Production Lot Completion]
VAR MaterialID = 'In-House Production Costs'[Material ID]

VAR LatestPurchaseDateBeforeProduction =
CALCULATE (
MAX ( 'GRIR Clearing Run Log'[Posting Date]),
'GRIR Clearing Run Log'[Posting Date]<= ProductionDate,
'GRIR Clearing Run Log'[Material ID]=MaterialID)

RETURN 
LatestPurchaseDateBeforeProduction
 
This seems to work for some rows but not all, since they return a blank. When i check the data manually by doing the filters "manually", there clearly is a match. 
 
What is wrong with the dax / are there options for this?
 
Thanks in advance!
Need Help
  • v-henryk-mstf's avatar
    v-henryk-mstf
    4 years ago

    Hi CarlsBerg999 ，

     

    For the filter function, its role is to get a table and return a table with the same columns as the original table, apply filter conditions row by row, and finally return the rows that meet the filter conditions.

    Whereas CALCULATE creates a new filtering context and then counts the value expressions in the new context, not adding filters to the formula will lead to inconsistencies in the calculation logic.

    1 =
VAR ProductionDate = 'A'[Production Lot Completion]
VAR MaterialID = 'A'[Material ID]
VAR Latest =
    CALCULATE (
        MAX ( 'GRIR'[Posting Date] ),
        'GRIR'[Posting Date] <= ProductionDate,
        'GRIR'[Material ID] = MaterialID
    )
RETURN
    Latest
    1 =
VAR ProductionDate = 'A'[Production Lot Completion]
VAR MaterialID = 'A'[Material ID]
VAR Latest =
    CALCULATE (
        MAX ( 'GRIR'[Posting Date] ),
        FILTER ( 'GRIR', ''[Posting Date] <= ProductionDate ),
        FILTER ( 'GRIR', 'GRIR'[Material ID] = MaterialID )
    )
RETURN
    Latest

     

     You can read related blog as below:

    Specifying multiple filter conditions in CALCULATE - SQLBI

     

    Best Regards,
    Henry


    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi try to put && instead of a coma after Production Date

  • CarlsBerg999's avatar
    CarlsBerg999
    Helper V
    4 years ago
    Adding Filters inside the calculate fixed the issue. Can anyone refer to guidance or explain why? I dont understand this.

    CALCULATE     (
    MAX ( 'GRIR Clearing Run Log'[Posting Date]),
    FILTER('GRIR Clearing Run Log','GRIR Clearing Run Log'[Posting Date]<= ProductionDate),
    FILTER('GRIR Clearing Run Log''GRIR Clearing Run Log'[Material ID]=MaterialID))
  • v-henryk-mstf's avatar
    v-henryk-mstf
    Community Support
    4 years ago

    Hi CarlsBerg999 ，

     

    For the filter function, its role is to get a table and return a table with the same columns as the original table, apply filter conditions row by row, and finally return the rows that meet the filter conditions.

    Whereas CALCULATE creates a new filtering context and then counts the value expressions in the new context, not adding filters to the formula will lead to inconsistencies in the calculation logic.

    1 =
VAR ProductionDate = 'A'[Production Lot Completion]
VAR MaterialID = 'A'[Material ID]
VAR Latest =
    CALCULATE (
        MAX ( 'GRIR'[Posting Date] ),
        'GRIR'[Posting Date] <= ProductionDate,
        'GRIR'[Material ID] = MaterialID
    )
RETURN
    Latest
    1 =
VAR ProductionDate = 'A'[Production Lot Completion]
VAR MaterialID = 'A'[Material ID]
VAR Latest =
    CALCULATE (
        MAX ( 'GRIR'[Posting Date] ),
        FILTER ( 'GRIR', ''[Posting Date] <= ProductionDate ),
        FILTER ( 'GRIR', 'GRIR'[Material ID] = MaterialID )
    )
RETURN
    Latest

     

     You can read related blog as below:

    Specifying multiple filter conditions in CALCULATE - SQLBI

     

    Best Regards,
    Henry


    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.