Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Grouping Dates

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 th...
az38's avatar
az38
Icon for Community Champion rankCommunity Champion
6 years ago

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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

 Hi Anonymous 

 

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

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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.