Create Measure Filtering Range of Column Value by Date Column in Different Table

[polanddm]

I have a data model with two tables.
Table EST has estimated value for X by Date
Table ACT has actual values for X by Date

I would like to create a new Measure (NewerX) that filters values from ACT based upon the latest (or max) Date in EST, but retain the Date of the values from ACT.

I tried this:

NewerX = CALCULATE(SUM(ACT[X]), filter(ACT,ACT[Date] > MAX(EST[Date])))

However, when I view the ACT[Date] & NewerX in a Table Visual, the Date appear to be the *first* Dates in ACT rather than the Dates that are > MAX(EST[Date])

Any thoughts I how to get the results desired?

Thanks,

Doug

[v-lid-msft]

Hi @polanddm ,

What is the relationship between two tables? we make  a sample and get the expected result:

Please try to set the ACT[Date] as not summarize, if it does not work, please try to use the following measure:

Measure = if(min(ACT[Date]) > Max(EST[Date]), SUM(ACT[X]), BLANK())

By the way, PBIX file as attached.

Best regards,

[polanddm]

Thank you for the help. 

I am still not getting the expected values.

Here is screenshot

NewerACWP = if(min('Jamis'[APEndDate]) > max('CobraSummary'[Date]) && min('Jamis'[APEndDate]) > min('CobraSummary'[Date]), sum('Jamis'[BurdenedCost]), BLANK())

 
The left visual is Jamis (ACT), middle visual is CobraSummary (EST) and right visual is measure.

ACT (Jamis) and EST (CobraSummary) are related through field called "TO" (in drop-down slicer).

Does this help any in discerning what I am doing wrong?

Thank you!

[v-lid-msft]

Hi @polanddm ,

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

Best regards,

[v-lid-msft]

Hi @polanddm ,

It seems because 'Jamis' table cannot filter the 'CobraSummary' Table correctly, if you have a middle table such as following:

'Jamis'[TO] M<--1 'TOTable'[TO] 1 -->M 'CobraSummary'[TO]

Please try to use the following code:

NewerACWP =
IF (
    MIN ( 'Jamis'[APEndDate] )
        > CALCULATE (
            MAX ( 'CobraSummary'[Date] ),
            FILTER (
                ALLSELECTED ( 'CobraSummary' ),
                'CobraSummary'[TO] IN DISTINCT ( 'TOTable'[TO] )
            )
        ),
    SUM ( 'Jamis'[BurdenedCost] ),
    BLANK ()
)

If you have relationship as following

'Jamis'[TO]  M<---->M 'CobraSummary'[TO]

Please try to use the following formula:

NewerACWP =
IF (
    MIN ( 'Jamis'[APEndDate] )
        > CALCULATE (
            MAX ( 'CobraSummary'[Date] ),
            FILTER (
                ALLSELECTED ( 'CobraSummary' ),
                'CobraSummary'[TO] IN DISTINCT ( 'Jamis'[TO] )
            )
        ),
    SUM ( 'Jamis'[BurdenedCost] ),
    BLANK ()
)

Or you can use the following:

NewerACWP =
IF (
    MIN ( 'Jamis'[APEndDate] )
        > CALCULATE ( MAX ( 'CobraSummary'[Date] ), ALLSELECTED () ),
    SUM ( 'Jamis'[BurdenedCost] ),
    BLANK ()
)

Best regards,