Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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

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

Need Help

4 Replies

  • v-lid-msft's avatar
    v-lid-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    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,

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        v-lid-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi Anonymous ,

         

        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,