cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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(...

 

 

 

7 REPLIES 7
v-frfei-msft
Community Support
Community Support

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] )
    )

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
v-frfei-msft
Community Support
Community Support

Hi @Anonymous ,

 

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

 

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

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.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Greg_Deckler
Super User
Super User

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
amitchandak
Super User
Super User

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
Not applicable

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]))
 

Capture.PNG

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.

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.