topic Help creating custom column based on time interval in DAX Commands and Tips

Help creating custom column based on time interval

Marco3007 — Thu, 18 May 2023 16:53:11 GMT

Greetings!

I have tried, without much knowledge other than some experience, to create a visual that displays incident tickets handled by my team.

I want to present how many tickets have been created and taken care of, either monthly or year-to-date, and that is easy but I would like to show intervals of when they were created. We have 2 shifts, one from 6:30AM - 4:00PM and the other 4:00PM - 1:15AM. I have a column with time and date, so I copied it and split it to get just the time (properly formatted as so), and now I would like to create a column that displays one of the following values:

6h - 8h30

8h30 - 11h

11h - 13h30

13h30 - 16h

16h - 19h

19h - 21h

21h - 2h

2h - 6h (out of bh)

I tried to apply the logic behind what I want with M in Power Query and DAX in Create Costum Column, but either way I think I am confusing a bit on the syntax of the rules. Basically, the idea is (disregard it as a code, it is just how I thought of it):

New Column Name = INC_CreationPeriod

INC_CreationPeriod = if (data on column) time >= 06:00:00 and time <= 08:29:59, then (fill in as) "6h - 8h30";

or if time >= 08:30:00 and time <= 10:59:59, then "8h30 - 11h";

or if time >= 11:00:00 and time <= 13:29:59, then "11h - 13h30";

...and so on for all the time intervals...

or if time >= 21:30:00 and time <= 01:59:59, then "21h - 2h";

and finally, if time does not fall within these parameters above, fill in as "2h - 6h (out of bh)"

I spent quite a while trying to figure it out, simplifying it as much as possible, careful when using if / else operators, trying to unscramble the time format, converting... but eventually getting caught in errors. I also tried little by little, but even when the first field (6h - 8h30) works, the others don't...

Any ideas on how to work around it?

I can get more details of the table if needed.

Thank you all very much!

Re: Help creating custom column based on time interval

Marco3007 — Thu, 18 May 2023 18:27:39 GMT

An example of what I expect (using made up data):

Re: Help creating custom column based on time interval

FreemanZ — Sat, 20 May 2023 10:43:07 GMT

hi Marco3007

try to add a calculated column like:

column = SWITCH( TRUE(), [INC_Time]<5/24, "2h- 5h(out of bh)", [INC_Time]<8.5/24, "6h- 8h30", [INC_Time]<11/24, "8h30- 11h", [INC_Time]<16/24, "13h30- 16h", [INC_Time]<19/24, "16h- 19h", "21h -22h" )

it worked like:

Re: Help creating custom column based on time interval

Marco3007 — Mon, 22 May 2023 11:58:08 GMT

Thank you very much, FreemanZ! I will try your suggestion. I had been struggling for a while and eventually made it work with AND function, as well as &&. In the end this is how I set it, but I will give it a try with your code, too:

INC_CreationPeriod = IF([INC_TimeOnly]>=time(6,0,0)&&[INC_TimeOnly]<=time(8,29,59), "6h - 8h30",

IF([INC_TimeOnly]>=time(8,30,0)&&[INC_TimeOnly]<=time(10,59,59), "8h30 - 11h",

IF([INC_TimeOnly]>=time(11,0,0)&&[INC_TimeOnly]<=time(13,29,59), "11h - 13h30",

IF([INC_TimeOnly]>=time(13,30,0)&&[INC_TimeOnly]<=time(15,59,59), "13h30 - 16h",

IF([INC_TimeOnly]>=time(16,0,0)&&[INC_TimeOnly]<=time(18,59,59), "16h - 19h",

IF([INC_TimeOnly]>=time(19,0,0)&&[INC_TimeOnly]<=time(21,29,59), "19h - 21h30",

IF([INC_TimeOnly]>=time(21,30,0)&&[INC_TimeOnly]<=time(1,59,59), "21h30 - 2h", "2h - 6h (out of BH)")))))))

Thanks a lot once again!