Forum Discussion

NilR's avatar
NilR
Post Patron
5 years ago
Solved

Create Automated Date Range

What is the best way to create below case statement in PBI? I do not want to hard code the date range. Would like to autmate the green font.       CASE WHEN DATE BETWEEN '2020-01-01' AND '2020-...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    5 years ago

    NilR Perhaps just set the display format for the DATE column to month and year, it's a default option under Column Tools when you have a date column.

smpa01's avatar
smpa01
Community Champion
5 years ago

NilR 

new column= SWITCH(TRUE(), 
                [Column1]>=DATE(2021,1,1)&&[Column1]<=DATE(2021,1,31),"jan",
                [Column1]>=DATE(2021,2,1)&&[Column1]<=DATE(2021,2,28),"feb",
               ............................................................
               [Column1]>=DATE(2021,12,1)&&[Column1]<=DATE(2021,12,31),"dec",
               "others")
NilR's avatar
NilR
Post Patron
5 years ago

I don't want to hard code the date. is it still possible?

  • smpa01's avatar
    smpa01
    Community Champion
    5 years ago

    NilR not sure what do you mean by that. You are hardcoding the date in SQL CASE statement as it stands