FirstDate/LastDate not being applied to current context

[LMSF]

I have an issue that might be down to my own lack of understanding of how FirstDate/LastDate work. As per the Microsoft Docs it should apply context and I assumed bring back a dynamic LastDate 

MS Docs Definition - "Returns the last date in the current context for the specified column of dates."

I have a data model with a fact table called AuditResults storing AuditIDs which is linked to an Audit dimension table holding the date of these audits. There is a second dimension table linked on Depot IDs which maps the sites audits were comleted at and contains site names and region names the sites live in. 

I'm simply trying to list Audit IDs in column 1 of a Table visual and the first date of the audits in the second column. But it always brings back the first date of ALL the AuditIDs. I was also trying to list the first date by Depot but it gives the same error. 

Below is my data model and an example of the error. I'm pulling in the AuditID from Fact Audit Results and a measure which is the following line of code...

DateMeasure = FIRSTDATE('Dim Audits'[AuditDate])

Any help is appreciated!

[v-yanjiang-msft · Accepted solution]

Hi @LMSF ,

Normally, FIRSTDATE will return the correct value, I think your error may be related to the relationship between the tables.

You can modify the formula like this, it will not be affected by the relationship..

DateMeasure =
MINX (
    FILTER ( 'Dim Audits', 'Dim Audits'[AuditID] = MAX ( 'AuditResults'[AuditID] ) ),
    'Dim Audits'[AuditDate]
)

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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

[amitchandak]

@LMSF , Depot is not joined Audit Dim. So we need force fact

DateMeasure = calculate(FIRSTDATE('Dim Audits'[AuditDate]), filter(Filter('Fact Audit Result', not(isblank('Fact Audit Result') ) ) )