cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NilR
Helper III
Helper III

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!

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


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

8 REPLIES 8
v-janeyg-msft
Community Support
Community Support

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
Resident Rockstar
Resident Rockstar

@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")
             

New Animated Dashboard: Sales Calendar


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

smpa01
Resident Rockstar
Resident Rockstar

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


New Animated Dashboard: Sales Calendar


Greg_Deckler
Super User IV
Super User IV

@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"
)

 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Does it drill down to weeks?

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.