Create Automated Date Range

[NilR]

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-01-31' THEN 'January 2020'
WHEN DATE BETWEEN '2020-02-01' AND '2020-02-29' THEN 'February 2020'
WHEN DATE BETWEEN '2020-03-01' AND '2020-03-31' THEN 'March 2020'
WHEN DATE BETWEEN '2020-04-01' AND '2020-04-30' THEN 'April 2020'
WHEN DATE BETWEEN '2020-05-01' AND '2020-05-31' THEN 'May 2020'
WHEN DATE BETWEEN '2020-06-01' AND '2020-06-30' THEN 'June 2020'
WHEN DATE BETWEEN '2020-07-01' AND '2020-07-31' THEN 'July 2020'
WHEN DATE BETWEEN '2020-08-01' AND '2020-08-31' THEN 'August 2020'
WHEN DATE BETWEEN '2020-09-01' AND '2020-09-30' THEN 'September 2020'
WHEN DATE BETWEEN '2020-10-01' AND '2020-10-31' THEN 'October 2020'
WHEN DATE BETWEEN '2020-11-01' AND '2020-11-30' THEN 'November 2020'
WHEN DATE BETWEEN '2020-12-01' AND '2020-12-31' THEN 'December 2020'
WHEN DATE BETWEEN '2021-01-01' AND '2021-01-07' THEN 'January 1st - 7th 2021'
WHEN DATE BETWEEN '2021-01-08' AND '2021-01-14' THEN 'January 8th - 14th 2021'
WHEN DATE BETWEEN '2021-01-15' AND '2021-01-21' THEN 'January 15th - 21st 2021'
WHEN DATE BETWEEN '2021-01-22' AND '2021-01-31' THEN 'January 22nd - 31st 2021'
WHEN DATE BETWEEN '2021-02-01' AND '2021-02-07' THEN 'February 1st - 7th 2021'
WHEN DATE BETWEEN '2021-02-08' AND '2021-02-14' THEN 'February 8th - 14th 2021'
WHEN DATE BETWEEN '2021-02-15' AND '2021-02-21' THEN 'February 15th - 21st 2021'
WHEN DATE BETWEEN '2021-02-22' AND '2021-02-28' THEN 'February 22nd - 28th 2021'
ELSE 'OTHER' END

 Thanks!

[v-janeyg-msft]

Hi, @NilR 

The above replies make sense, but it seems not what you want. Do you have a date column? Do you want to create a new date column in month format and week format? Or how do you want to use it? Can you share your thoughts or  your desired result? So we can help you soon.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[smpa01]

@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]

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

[smpa01]

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

[Greg_Deckler]

@NilR - In DAX you could use a SWITCH TRUE statement. In Power Query you would need nested if then else statements, so something like the following in DAX:

SWITCH(TRUE(),
 [DATE] >= DATE(2020,1,1) && [DATE] <= DATE(2020,1,31),"January 2020",
 [DATE] >= DATE(2020,2,1) && [DATE] <= DATE(2020,2,29),"February 2020",
...,
 "Other"
)

[NilR]

I don't want to hard code the date. what do you suggest?

[Greg_Deckler · Accepted solution]

@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.

[NilR]

Does it drill down to weeks?