Forum Discussion

DanAub's avatar
DanAub
New Member
3 years ago
Solved

DAX query comparing dates across tables

I have a table Projects like this

Project

Start Date

 

This has a many to one relationship with the table Milestones like this

Project

Milestone Name

Milestone Date

 

I want to compare Start Date in the Projects table to Milestone Date in the Milestones table where Milestone Name = “Example”. If they are the same return “Same” if they are different return “Diff”

 

I’ve tried writing this in Dax but I just can’t figure it out – can anyone help?

  • v-jialluo-msft's avatar
    v-jialluo-msft
    3 years ago

    Hi DanAub ,

     

    Please follow these steps:

    (1)This is my test data :

     

     

    (2) Create a measure to judgment

    judgment =
VAR _time =
    MAXX (
        FILTER ( Milestones, Milestones[Milestone Name] = "Example" ),
        Milestones[Milestone Date]
    )
VAR _time2 =
    MAXX (
        FILTER ( Projects, Projects[Porject] = MAX ( Milestones[Project] ) ),
        Projects[StarDate]
    )
RETURN
    IF ( _time = _time2, "Same", "Diff" )

    (3) The result is as follows :

     

    If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the question.

     

    Best Regards,

    Gallen Luo

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

2 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Icon for Community Champion rankCommunity Champion
    3 years ago

    CalculatedColumn=IF(MINX(FILTER(RELATEDTABLE(Milestones),Milestones[Milestone Name]="Example"),Milestones[Milestone Date])=Projects[Start Date],"Same","Diff")

    Measure=VAR _sd=MAX(Projects[Start Date]) RETURN IF(CALCULATE(MIN(Milestones[Milestone Date]),Milestones[Milestone Name]="Example")=_sd,"Same","Diff")

  • v-jialluo-msft's avatar
    v-jialluo-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi DanAub ,

     

    Please follow these steps:

    (1)This is my test data :

     

     

    (2) Create a measure to judgment

    judgment =
VAR _time =
    MAXX (
        FILTER ( Milestones, Milestones[Milestone Name] = "Example" ),
        Milestones[Milestone Date]
    )
VAR _time2 =
    MAXX (
        FILTER ( Projects, Projects[Porject] = MAX ( Milestones[Project] ) ),
        Projects[StarDate]
    )
RETURN
    IF ( _time = _time2, "Same", "Diff" )

    (3) The result is as follows :

     

    If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the question.

     

    Best Regards,

    Gallen Luo

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