Calculating measure with custom filters

[parimal]

I have a data model with 2 tables - DimCustomer and FactTable

DimCustomer has {Customer, Customer Name}

FactTable has {Material, Customer, DataSource, Date, Quantity, Value} 

There are 2 data sources - SourceA, SourceB.

I'm trying to get a measure that calculates the Max Date for each Customer from SourceA only. 

I wrote a measure formula

Last Date Measure = CALCULATE(MAX(FactTable[Date]),KEEPFILTERS(FactTable[DataSource]="SourceA"))

But this doesn't work as expected in all scenarios especially when there is another filter on the report.

So I added a calculated column in my DimCustomer Table with the same formula - 

Last Date Col = CALCULATE(MAX(FactTable[Date]),KEEPFILTERS(FactTable[DataSource]="SourceA"))

This one works fine.. Example below .. I have one material selected - the column formula gives correct answer, measure doesn't.

What's the correct way to write this measure formula such that it gives me the expected answer?

[V-pazhen-msft · Accepted solution]

@parimal 

Create the following measure:

measure = calculate(max(table[date]),filter(all(table),sumx(filter(table,earlier(table[customer])=table[customer]),1)),table[Source]="Source A")

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

[parimal]

this is my fact table for customer 19.. there are 2 rows, and no matter which material i chose, i want the answer to be 30-sep-2019.

[karun_r]

Please try this measure and let me know if it works

Max Date from SourceA = CALCULATE(MAX(Source[Date]), ALL(FactTable[Material]), DataSource = "SourceA")

[parry2k]

@parimal add column using following expression.

Source A date = 
CALCULATE ( 
    MAX ( Source[Date] ), 
    ALLEXCEPT ( Source, Source[Customer] ), 
    Source[Source] = "Source A" 
)

  Would appreciate Kudos 🙂 if my solution helped.