Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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
  • jaipal's avatar
    jaipal
    4 years ago

    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

TheoC's avatar
TheoC
Icon for Community Champion rankCommunity Champion
4 years ago

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's avatar
Anonymous
Not applicable
4 years ago

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's avatar
    TheoC
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago
      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