Forum Discussion

aatish178's avatar
aatish178
Icon for Helper IV rankHelper IV
2 years ago

comparing 2 date columns from 2 different tables with each other

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    Anonymous Ashish_Mathur dufoq3 Anonymous 

Need Help
Tips and Tricks

3 Replies

  • dufoq3's avatar
    dufoq3
    Icon for Community Champion rankCommunity Champion
    2 years ago

    @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

     

  • aatish178's avatar
    aatish178
    Icon for Helper IV rankHelper IV
    2 years ago

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