New column with dates after 1/9/2021

[Anonymous]

I am trying to create a new column that will just show me data from a date column if the date is greater than 1/9/2021. Please can you help me. Many thanks

[TheoC]

@Anonymous if you want all data to be removed entirely, then @jaipal has provided a good solution using Power Query.  If you want to keep all of your records and just have a column that contains dates which are greater than 1/9/2021, then you can create a Calculated Column that uses the below formula:

Calculated Column = 

SWITCH ( 
   TRUE () , 
     'Table'[Date] < DATE ( 2021 , 9 , 1 ) , 'Table'[Date] , 
      BLANK() 
        )

Thanks,

Theo

[Anonymous]

Thank you so much thats fab. I was also trying to get this to work with another collumn that counts the detentions only if they are after 1/9/2021 something like this..... however im not sure where i am going wrong.

collumn = If ('Detentions (2)'[after sept]>date(2021,09,01),'Detentions (2)'[Detention Date]),CALCULATE(SUM('Detentions (2)'[Payback]),ALLEXCEPT('Detentions (2)','Detentions (2)'[Adno]))+0 

The first section works ok similar to your solution to seperate just the dates after 01/09/2021. please would it be posible to help me with this too. Many thanks

[TheoC]

Hi @Anonymous 

Add this part as its own measure: CALCULATE(SUM('Detentions (2)'[Payback]),ALLEXCEPT('Detentions (2)','Detentions (2)'[Adno]))+0 

Then just add the measure as the "else" in your if statement.

Hope that makes sense.

Theo

[Anonymous]

Sorry im not sure where to put the ELSE, I have both of these collumns working ok on their own, but i want them to work together in one collumn?

Collumn name = If ('Detentions (2)'[after sept]>date(2021,09,01),'Detentions (2)'[Detention Date]) ELSE, CALCULATE(SUM('Detentions (2)'[Payback]),ALLEXCEPT('Detentions (2)','Detentions (2)'[Adno]))+0

[jaipal · Accepted solution]

@Anonymous  in the power query, right click on date column and click on Date Filters-->After

you can manage the data by giving the Date