Forum Discussion
New column with dates after 1/9/2021
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
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
5 Replies
- TheoC
Community Champion
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
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]))+0The 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
jaipalResolver III
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
