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

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