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
Solved! Go to Solution.
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.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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")
New Animated Dashboard: Sales Calendar
@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" )
Check out new user group experience and if you are a leader please create your group
Click here to read more about the April 2021 Updates!
100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.
See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.