Forum Discussion
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-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!
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.
8 Replies
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.
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" )I don't want to hard code the date. what do you suggest?
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.
