Grouping Dates

[Anonymous]

So I’m working with date-data and want to make a new column (I will use this as a slicer in my report) to group dates according to following condition :

If date between 01-10-2019 and 22-10-2019 then group A

If date between 08-10-2019 and 29-10-2019 then group B  ** Here i want the overlapping dates from group A as well.

If date between 15-10-2019 and 05-11-2019 then group C and So on.  ** Here i want the overlapping dates from group A and group B as well.

I have tried the IF, OR, AND logic but it is just working for the first group.

Please let me know if anybody have any other function in your mind which could solve this issue.

[az38]

Hi @Anonymous 

Try to SWITCH, it will stop execute after first succesfull conditionand will help you to resolve overlapping issue

DateGroup = SWITCH(TRUE(),
DATESBETWEEN([Date], DATE(2019,10,1), DATE(2019,10,22)), "A",
DATESBETWEEN([Date], DATE(2019,10,8), DATE(2019,10,29)), "B",
DATESBETWEEN([Date], DATE(2019,10,15), DATE(2019,11,5)), "C",
"Undefined"
)

do not hesitate to give a kudo to useful posts and mark solutions as solution

[spandy34]

Hi

I have a similar query but I was wondering if, instead of having to update the values each year, we could set a start date say 11/10/21 and then every 28 days a new Period - like the example below.  I need them as period numbers based on a DateofVIsit value - and then for it to start back at Period 1 for the next year after 13 Periods.

[Anonymous]

I'm getting the above error while using SWITCH(). 

@az38 

[az38]

Sorry, @Anonymous 

I gave you completely wrong statement

try

DateGroup = SWITCH(TRUE(),
[Date] >= DATE(2019,10,1) && [Date] <=  DATE(2019,10,22)), "A",
[Date] >= DATE(2019,10,8) && [Date] <=  DATE(2019,10,29)), "B",
[Date] >= DATE(2019,10,15) && [Date] <=  DATE(2019,11,5)), "C",
"Undefined"
)

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Anonymous]

hi @az38  @Anonymous 

let say date is 2019/10/16. 

this date comes in all buckets but as per column it will always show "A" only. The first condtion of switch statement get satisfied and it will not check for next conditions.

@Anonymous  if you want the same solution then above solution will work.

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[Anonymous]

 Hi @Anonymous 

what value you are expecting for 16-10-2019?

[Anonymous]

HI @Anonymous  I'm working with Sales data and here I want cumulative week as following :

So, Group "A" will show sales from 1, Oct 2019 to 22,Oct 2019

      Group "B" will show sales from 8, Oct 2019 to 29,Oct 2019 ** Here I want sales of last three weeks from group "A"

      Group "C" will show sales from 15, Oct 2019 to 05,Nov 2019 ** Here I want sales of from 15 till 05

      Group "D" will show sales from 22, Oct 2019 to 12,Nov 2019 and so on.

I will use these groups as a slicer or in bar chart to show/ filter sales according to week bucket.