Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Measure filtered by dates

Hello - My measure below returns results as expected until the underlined part. I need to add more filtering with dates that I can't seem to do. I want to be able to say X date (related table) is less than Y date. Thoughts? Thanks!

 

Measure 2 = CALCULATE(COUNT(Opportunity[OpportunityId]),USERELATIONSHIP(Opportunity[ParentContactId],altus_leadstage[altus_Contact]),altus_leadstage[altus_name]="MQL" && DATEVALUE(altus_leadstage[altus_Date])<=DATEVALUE(...

 

 

 

Need Help

7 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    You should be use like

    Table1[Date] = related(Table2[Date])

    Or

    Table1[Date] =Max(Table2[Date])

     

    Please provide the complete clause for more help.

    Can you share sample data and sample output.

     


    Appreciate your Kudos.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks for the response and sorry for my delayed response. Still getting an error...

       

      Measure 2 = CALCULATE(COUNT(Opportunity[OpportunityId]),USERELATIONSHIP(Opportunity[ParentContactId],altus_leadstage[altus_Contact]),altus_leadstage[altus_name]="MQL" && altus_leadstage[altus_Date]<MAX(Opportunity[CreatedOn]))
       

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        6 years ago

        Hi,

        Try this measure

        Measure 2 = CALCULATE(COUNT(Opportunity[OpportunityId]),USERELATIONSHIP(Opportunity[ParentContactId],altus_leadstage[altus_Contact]),FILTER(altus_leadstage,altus_leadstage[altus_name]="MQL"&&altus_leadstage[altus_Date]<MAX(Opportunity[CreatedOn])))

        If it does not help, then share the link from where i can download your PBI file.  Clearly show the problematic measure there.

         

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    Please make your data type of your date columns to be the same and check again.

     

     

    • v-frfei-msft's avatar
      v-frfei-msft
      Community Support
      6 years ago

      Hi Anonymous ,

       

      Has your issue been solved? If so kindly mark my answer as a solution to close the case. Thanks in advance. Any other question, feel free to let me know please.

       

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    Please try this one.

    Measure 2 =
VAR d =
    MAX ( Opportunity[CreatedOn] )
RETURN
    CALCULATE (
        COUNT ( Opportunity[OpportunityId] ),
        FILTER (
            altus_leadstage,
            altus_leadstage[altus_name] = "MQL"
                && altus_leadstage[altus_Date] < d
        ),
        USERELATIONSHIP ( Opportunity[ParentContactId], altus_leadstage[altus_Contact] )
    )