Creating a custom column to filter data

Question

Hello.&nbsp;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&nbsp;Title | Category | Date&nbsp;A&nbsp; &nbsp; &nbsp;| E&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | 2017-01-01B&nbsp; &nbsp; | F&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| 2023-01-02C&nbsp; &nbsp;| Z&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| 2023-01-01D&nbsp; &nbsp;| X&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| 2015-01-01&nbsp;I have created a new column called Category, which statesIf Category = E then Category A&nbsp;Else if Category = F then Category A&nbsp;Else If&nbsp; Category B&nbsp;This part all works fine.&nbsp;&nbsp;The bit i need help with is excluding data if category B data date is greter than 2 years&nbsp;I need to create a column (unless there is a better way to do it) that says&nbsp;If category column = Category B AND Date is greater then 2 years, then write the word exclude, else write the word Include.&nbsp;&nbsp;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..&nbsp;I can then put a filter on new column to filter on exclude.&nbsp;&nbsp;Can someone please help me write the code to exclude 2 dynamic years&nbsp; worth of data is category = category B&nbsp;&nbsp;&nbsp;

ppstar · Answer

HI, Thanks for your reply.&nbsp;I get a Token ',' expected with this.&nbsp;It highlighting the in&nbsp;&nbsp;&nbsp;

samwiseowl · Answer

Hi&nbsp;PPStar&nbsp;DateDiff lets you work out the difference in days/months/years etcSwitch =SWITCH(&nbsp; &nbsp; TRUE()&nbsp; &nbsp; ,[Category] in {"E","F"}, "Category A"&nbsp; &nbsp; ,DATEDIFF([Date], TODAY(),YEAR) &lt;2, "Category B", "Exclude")

samratpbi · Answer

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(
&nbsp; &nbsp; AND(DateCategory[Date] &lt; _currDate2,DateCategory[Category] = "B"),
&nbsp; &nbsp; "Exclude",
&nbsp; &nbsp; "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 · Answer

Apologises&nbsp;PPStar&nbsp;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] &gt;= Date.AddYears(Date.From( DateTimeZone.LocalNow()),-2) then "Category B" else "Exclude")&nbsp;&nbsp;

kedar_pande · Answer

PPStar&nbsp;DAX formula you can use for the new column:Status = IF('YourTable'[Category] = "Category B" &amp;&amp; 'YourTable'[Date] &lt; 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".&nbsp;💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉Cheers,KedarConnect on LinkedIn

Forum Discussion

Creating a custom column to filter data

8 Replies

Related content

Creating a custom column

create custom column concatenated

Creating Custom columns

Create a custom column

Create columns group