How to calculate timeslots column in DAX?

[CK-NL]

Hello all,

I have a table as follows, spanning several years (this is an exerpt):

Date	Year	Quarter	Monthnumber	Timeslot
1-7-2022	2022	Q3	7
2-7-2022	2022	Q3	7
3-7-2022	2022	Q3	7
4-7-2022	2022	Q3	7
5-7-2022	2022	Q3	7
6-7-2022	2022	Q3	7

It is used in a dashboard that shows the results of a questionnaire that is filled in periodically by people all over the organisation. This data is aggregated into scores and I want to show trends. For visualisation purposes I want to cluster responses over a specific period into one 'timeslot', one point at which the organisation is measured. So I can compare it to other 'timeslots'. 

The questionnaire is roughly run from September until March so I want to make a cutoff in June and put everything between July and June in a timeslot 1, the space in between the next July and June in timeslot 2, and so forth.

The date table is a calculated table that is attuned to the minimal and maximal date in the dataset.

What is the best way to create this timeslot column in DAX? 

[Ashish_Mathur]

Hi,

Create a Calendar Table with a calculated column to extract the Month number.  In another calculated column, write this formula

Year slot = if(calendar[month number]>=8,year(calendar[Date])&"-"&year(calendar[Date])+1,year(calendar[Date])-1&"-"&year(calendar[Date]))

Create a relationship (Many to One and Single) from the Date column of your Data Table to the Date column of your Calendar Table.

Hope this helps.

[CK-NL]

To keep my data lean and mean I have a calculated date table (DAX-generated). I need to do it in DAX because it needs to be dynamic to remain in sync with the dates in the table.

You're saying to drop the calculated table and add a static calendar table where this column is a part of?

[lbendlin]

I need to do it in DAX because it needs to be dynamic to remain in sync with the dates in the table.

not buying this argument.  Dates tables tend to be small, a couple thousand rows max. It is very unlikely that they will create something like a Feb 30th any time soon. 

From my experience (and YMMV of course) implementing calendar tables in DAX or Power Query is a waste of time (pun not intended).

[CK-NL]

@lbendlin I am very much inclined to lean your way because I agree with you that a calender table can never be that large. And I am very sure my mileage is dwarfed by yours.

However, why is this dynamic table concept taught in every Power BI course? As to avoid as much data redundancy as possible.

[lbendlin]

I guess it's a nice finger exercise, and ADDCOLUMNS and FORMAT will come in handy in other situations. Tool knowledge is important for sure, but it is also important to know when not to use a particular tool.

[lbendlin · Accepted solution]

The best way is to NOT do this in DAX.  Use an external calendar table, add the column there, and then consume that table as part of your dataset.