Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
CarlsBerg999
Helper V
Helper V

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!
1 ACCEPTED SOLUTION
v-henryk-mstf
Community Support
Community Support

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.

View solution in original post

4 REPLIES 4
v-henryk-mstf
Community Support
Community Support

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.

CarlsBerg999
Helper V
Helper V

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))
Anonymous
Not applicable

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

Unfortunately this did not help my case.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.