Creating a custom column to filter data

[PPStar]

Hello. 

I have a very very large table being imported into my dataflow. i have a lot of columns, but for this post, i will shorten it with less columns

Title | Category | Date 

A     | E              | 2017-01-01

B    | F               | 2023-01-02

C   | Z               | 2023-01-01

D   | X               | 2015-01-01

I have created a new column called Category, which states

If Category = E then Category A 

Else if Category = F then Category A 

Else If  Category B

This part all works fine. 

The bit i need help with is excluding data if category B data date is greter than 2 years

I need to create a column (unless there is a better way to do it) that says 

If category column = Category B AND Date is greater then 2 years, then write the word exclude, else write the word Include. 

The 2 years needs to be dynamic and not hard coded, i.e. it needs to look at the current date and subtract 2 years.. 

I can then put a filter on new column to filter on exclude. 

Can someone please help me write the code to exclude 2 dynamic years  worth of data is category = category B 

[Kedar_Pande]

@PPStar 

DAX formula you can use for the new column:

Status = 
IF(
'YourTable'[Category] = "Category B" && 
'YourTable'[Date] < EDATE(TODAY(), -24),
"Exclude",
"Include"
)

You can now apply a visual-level filter or page-level filter to show only rows where Status is "Include".

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[PPStar]

Hello. 

This gives me an error,

Expression.Error: The import IF matches no exports. Did you miss a module reference?

Details

Reason = Expression.Error

also what is the EDATE?

[Anonymous]

Hi @PPStar ,

Check to make sure you are copying the dax code provided by Kedar_Pande  correctly, it should work fine.'' Edate' function works better for your needs, if you simply use 'Today - 720', you may get an error because leap year has 366 days.

Column = IF([_category] = "B" && [Date] < EDATE(TODAY(),-24) ,"exclude","include")

Best Regards,
Wenbin Zhou

[Angith_Nair]

@PPStar 

Use this DAX formula:

Status = 
IF(
    TableName[Category] = "Category B" && TableName[Date] < TODAY() - 730,
    "Exclude",
    "Include"
)

TODAY() - 730 dynamically calculates a 2-year range by subtracting 730 days from today’s date.

You can then filter your data based on the "Exclude" or "Include" status in the newly created column.

[samratpbi]

Hi, if you dont need those at all for your visuals, then better to filter those out in Power Query ( or source table). However in DAX, you may create below column:

Inc exc =

VAR _currDate = TODAY()

VAR _currDate2 = EDATE(_currDate,-24)

RETURN

IF(

    AND(DateCategory[Date] < _currDate2,DateCategory[Category] = "B"),

    "Exclude",

    "Include"

)

This is how it looks after adding new column :

Hope this helps! If this help to resolve your problem, then please mark it as solution, thanks

[SamWiseOwl]

Hi @PPStar 

DateDiff lets you work out the difference in days/months/years etc

Switch =

SWITCH(

    TRUE()

    ,[Category] in {"E","F"}, "Category A"

    ,DATEDIFF([Date], TODAY(),YEAR) <2, "Category B", "Exclude")

[PPStar]

HI, Thanks for your reply.

I get a Token ',' expected with this. 

It highlighting the in 

[SamWiseOwl]

Apologises @PPStar 

I missed you said query editor

= Table.AddColumn(#"Renamed Columns", "swap", each if [Category] = "E" then "Category A" else if [Category] = "F" then "Category A" else if [Date] >= Date.AddYears(Date.From( DateTimeZone.LocalNow()),-2) then "Category B" else "Exclude")