comparing 2 date columns from 2 different tables with each other

[aatish178]

Hi All, I am new to power bi , and in the start itself I have got a complex requirement

I have 2 tables consisting of date field in each as shown below:

Table 1:

DateToBeChecked

2024-05-05

2024-04-25

2024-03-13

2024-03-03

2024-02-11

Table 2:

DateToBeCompared

2024-05-31

2024-04-28

2024-03-20

2024-03-10

2024-02-15

There is no active relationship between 2 tables.

Expected Result:

I want to check whether DateToBeChecked<=DateToBeCompared, if the condition is true then I want to show that result, also if condition is false then I want to show Result as blank for higher dates along with less than dates. For Example:

Scen1: DateToBeCompared IS 2024-04-28 Then result should be:

DateToBeChecked

2024-04-25

2024-03-13

2024-03-03

2024-02-11

Scen2: DateToBeCompared=2024-03-20 Then result should be:

DateToBeChecked

2024-03-13

2024-03-03

2024-02-11

Scen3: DateToBeCompared IS 2024-03-10 Then result should be:

DateToBeChecked

2024-03-03

2024-02-11

To try this out I have used if statement along with selectedvalue(DateToBeCompared) but getting result as blank.

Can someone please help?

@quantumudit @quantumudit1    @v-jialongy-msft @Ashish_Mathur @dufoq3 @v-tianyich-msft 

[aatish178]

hi all, can someone plz help me with some possible solution

[aatish178]

Hi, thanks..I want to implement this logic for front end table visual

[dufoq3]

@Hi @aatish178, you asked in POWER QUERY forum so here you have PQ solution:

Result:

let
    DateToBeChecked = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtE1MAUipVgdGNdE1wiZa6xraIzCNUDmGukaGirFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    DateToBeCompared = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtE1MNU1NlSK1YFxTXSNLJC4xrpGBihcQ2Suka6hqVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    ComparedDates = List.Accumulate(
        DateToBeCompared[Date],
        DateToBeChecked,
        (s,c)=> Table.AddColumn(s, c, each if Date.From([Date]) <= Date.From(c) then c else null , type text)
),
    RenamedColumns = Table.RenameColumns(ComparedDates,{{"Date", "▼Checked | Compared ►"}})
in
    RenamedColumns