Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
aatish178
Helper III
Helper III

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    @v-jialongy-msft @Ashish_Mathur @dufoq3 @v-tianyich-msft 

3 REPLIES 3
aatish178
Helper III
Helper III

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

aatish178
Helper III
Helper III

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

dufoq3
Super User
Super User

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

 

Result:

dufoq3_0-1717048033588.png

 

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

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.