Forum Discussion

Achartuni's avatar
Achartuni
Helper I
4 years ago

Compare against filtered Column

Hello everybody,

 

I am having troubles comparing dates. I have the following table: (FORMAT = DAY-MONTH-YEAR)

WeekDate
01-1-2022
02-1-2022
03-3-2022
12-1-2022
23-2-2022

 

I need to compare the dates with week = 0 and output the text "Prior" IF the date is earlier than the earliest date in week 1.

Also, if the date in week = 0 is later than the latest date in the largest numbered week, in this case is week = 2, I need to output in a column the text "After".

 

Any help? 

 

 

Need Help
Show and Tell
Tutorial Requests

1 Reply

  • parry2k's avatar
    parry2k
    Super User
    4 years ago

    Achartuni add column using following expression, tweak it as you see fit:

     

    New Column = 
VAR __currentWeekMinDate = CALCULATE ( MIN ('Table'[Date] ), REMOVEFILTERS ( 'Table' ), 'Table'[Week] = 1 )
VAR __currentWeekMaxDate = CALCULATE ( MAX ('Table'[Date] ), REMOVEFILTERS ( 'Table' ), 'Table'[Week] > 1 )

RETURN 
SWITCH ( TRUE (),
    'Table'[Week] = 1, "Current", 
    'Table'[Date] <= __currentWeekMinDate, "Prior",
    'Table'[Date] >= __currentWeekMaxDate, "After",
    "Other"
)

     

     

    ✨ Follow us on LinkedIn and  to our YouTube channel

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

     

    ⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.