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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
alya1
Advocate II
Advocate II

Flag if any dates in 5 separate columns are within 60 days of each other (disregarding duplicates)

Hello,

I have table such as below: 

Start Date Date 1 Date 2 Date 3 Date 4 Date 5 Product ID
1/1/20  1/3/201/3/20 1
5/5/20 9/9/23  5/20/24 2
8/8/201/1/24 2/1/24  3
11/11/20 5/20/24 6/1/246/1/244
5/6/205/6/241/10/252/1/25  5

with many more rows.

 

Does anyone know how to flag a row where the dates in columns Date 1 - Date 5 (disregarding Start Date) are within 60 days of each other (disregarding duplicate dates)? 

Goal:

Start Date Date 1 Date 2 Date 3 Date 4 Date 5 Product ID Flag 
1/1/20  1/3/201/3/20 1no
5/5/20 9/9/23  5/20/24 2no
8/8/201/1/24 2/1/24  3yes
11/11/20 5/20/24 6/1/246/1/244yes
5/6/205/6/241/10/252/1/25  5yes

Thank you so much!

1 ACCEPTED SOLUTION

it's better to do the data transform. pls see the workaround below





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

4 REPLIES 4
ryan_mayu
Super User
Super User

why we flag no for the first row? is that because the dates are duplicated?

 

maybe you can try this

select startdate and product id columns, then unpivot other columns

11.png

 

then create a column

Column =
VAR _min=CALCULATE(min('Table'[Value]),ALLEXCEPT('Table','Table'[Product ID]))
var _max=CALCULATE(max('Table'[Value]),ALLEXCEPT('Table','Table'[Product ID]))
return if(_max=_min,"no",if(DATEDIFF(_min,_max,DAY)<60,"yes","no"))
 
12.PNG
 
pls see the attachment below

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




I am trying to use your new column as inspiration and came up with the following. However, everything is yes, perhaps due to blanks? Would you have any idea on how to improve it please?

SWITCH(
    TRUE(),
    DATEDIFF(date 1,date 2,DAY)<60, "yes",
    DATEDIFF(date 1,date 3,DAY)<60, "yes",
    DATEDIFF(date 1,date 4,DAY)<60, "yes",
    DATEDIFF(date 1,date 5,DAY)<60, "yes",
    DATEDIFF(date 2,date 3,DAY)<60, "yes",
    DATEDIFF(date 2,date 4,DAY)<60, "yes",
    DATEDIFF(date 2,date 5,DAY)<60, "yes",
    DATEDIFF(date 3,date 4,DAY)<60, "yes",
    DATEDIFF(date 3,date 5,DAY)<60, "yes",
    DATEDIFF(date 4,date 5,DAY)<60, "yes",
    "no"
    )

Hi ryan_mayu, thank you for your response! I do have a few follow ups please:

1) Yes, first row is not flaged due to duplicate; please disregard duplicates.

2) Sadly Min and Max will not work for all rows in data. I added Product ID 5 just now where there are 3 different dates across Date 1 - Date 5 columns and the middle date is wihtin 60 days of max date. I'm not sure if in the future we may have 5 different dates in all 5 columns. 

it's better to do the data transform. pls see the workaround below





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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