Date in one table is greater than date in another

[Ttaylor9870]

Hey All, Good Afternoon!

Just wondering has anyone ever came across this before. How would I go about comparing is the date in for exmaple tbl_1[Date Secured] is before tbl_2[Date Recieved]. Result should return true or false.

Both tables linked by a Sales_ID field.

Any help would be greatly appreicated! 🙂

Many Thanks,

Taylor

[Anonymous · Accepted solution]

Hi @Ttaylor9870 ,

Have you solved your problem?
If not, please try this DAX to create a new column:

DateComparisonResult = 
VAR RelatedDateReceived = RELATED(tbl_2[Date Recieved])
RETURN IF(tbl_1[Date Secured] < RelatedDateReceived, TRUE, FALSE)

The result is as follows:

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

[Fowmy]

@Ttaylor9870 

Provide the context, is the measure you are creating or a column? Some details will help

[Ttaylor9870]

@Fowmy 

See example below...
tbl_1

Sales_ID	Date Secured
12345	11/11/2023
12346	12/11/2023
12347	13/11/2023

tbl_2...

Sales_ID	Date Recieved
12345	12/11/2023
12346	13/11/2023
12347	13/11/2023

Both table above will be linked by the Sales_ID column tbl_1 being the fact table & tbl_2 being the dimension table. This is also just random sample data.

All I'd like to do is write a meausre or column whatever works so I can display if tbl_1[Date Secured] is before tbl_2[Date Recieved] or not. 🙂

Many Thanks,

Taylor

[ray_codex]

I think this will work as a measure:

VAR tbl = SUMMARIZE (
    Sales,
    Sales[Sales_ID],
    "IsBefore",
        IF (
            INT ( MAX (tbl_1[Date Secured]) - MAX (tbl_2[Date Recieved]) ) < 0,
            1,
           0
        )
)
RETURN
MAXX(
tbl, [Isbefore])

It returns a 1 if it is, 0 if it isn't.