Dax to compare two rows of two columns

[Anonymous]

Hi Community,

I need help with DAX to compare two rows of two columns. for a client if the category is the same & there is more than one date_closed_on, if date_closed_on in first row=date_opened_on in second row then return the date_closed_on from second row.

In the following example, for client 123, category A, if date_closed_on in first row(15/08/2020)=date_opened_on in second row (15/08/2020) then return the date_closed_on(20/08/2020) from second row.

So, for clients 324 & 678 the condition is false & it returns individual closed dates for each row.

Thanks in advance!

[Greg_Deckler · Accepted solution]

@Anonymous - For this to work, your data would need to look like the following, you can use Fill Down in Power Query.

Child ID	Category	date_opened_on	date_closed_on
123	a	2/8/2020	15/8/2020
123	a	15/8/2020	20/8/2020
324	b	2/8/2020	15/8/2020
324	b	16/8/2020	20/8/2020

You may also need an Index column, but for now you could do this:

Column = 
  VAR __Table = FILTER('Table',[Child ID]=EARLIER([Child ID]) && [Category]=EARLIER([Category]) && [date_opened_on] = EARLIER([date_closed_on]))
RETURN
  IF(ISBLANK(__Table),BLANK(),MAXX(__Table,[date_closed_on))

[mohammedadnant]

@Anonymous ,

Pls check the below steps all in Power Query Editor,

a. Use fill down to fill the blank values in Client ID

b. Add 2 in index column starts from 0 and starts from 1

c. Merge this table with the same table map Index 1 on top to Index 0 below table

d. conditional column to define your logic

Details:

Step: a & b

let
Source = Table.....,
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Child ID", Int64.Type}, {"Category", type text}, {"date_opened_on", type date}, {"date_closed_on", type date}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1, Int64.Type)
in
#"Added Index1"
Step: c & d

let
Source = Table.NestedJoin(MaxCloseDate, {"Index.1"}, MaxCloseDate, {"Index"}, "MaxCloseDate", JoinKind.LeftOuter),
#"Expanded MaxCloseDate" = Table.ExpandTableColumn(Source, "MaxCloseDate", {"Child ID", "Category", "date_opened_on", "date_closed_on"}, {"MaxCloseDate.Child ID", "MaxCloseDate.Category", "MaxCloseDate.date_opened_on", "MaxCloseDate.date_closed_on"}),
#"Added Conditional Column" = Table.AddColumn(#"Expanded MaxCloseDate", "Final Close Date", each if [date_closed_on] = [MaxCloseDate.date_opened_on] then [MaxCloseDate.date_closed_on] else [date_closed_on]),
#"Removed Other Columns" = Table.SelectColumns(#"Added Conditional Column",{"Child ID", "Category", "date_opened_on", "Final Close Date"})
in
#"Removed Other Columns"

---------------------------------------------------------------------------------------------------------------------------------------

If this solves your issue, pls marks as complete and hit the like button

Thanks & Regards,

Mohammed Adnan

www.youtube.com/taik18

[amitchandak · Accepted solution]

@Anonymous , this same as the Continuous Streak problem. Do not use +1, -1 of the gap

https://community.powerbi.com/t5/Quick-Measures-Gallery/Power-BI-Continuous-Streak-One-Day-Difference/m-p/1290382#M592

[Greg_Deckler · Accepted solution]

@Anonymous - For this to work, your data would need to look like the following, you can use Fill Down in Power Query.

Child ID	Category	date_opened_on	date_closed_on
123	a	2/8/2020	15/8/2020
123	a	15/8/2020	20/8/2020
324	b	2/8/2020	15/8/2020
324	b	16/8/2020	20/8/2020

You may also need an Index column, but for now you could do this:

Column = 
  VAR __Table = FILTER('Table',[Child ID]=EARLIER([Child ID]) && [Category]=EARLIER([Category]) && [date_opened_on] = EARLIER([date_closed_on]))
RETURN
  IF(ISBLANK(__Table),BLANK(),MAXX(__Table,[date_closed_on))