Sequential 03-20-2018 12:06 PM - last edited 06-27-2018 10:21 AM

[Greg_Deckler]

Suppose you have a list of years and weeks in those years (or months). Often, it would be nice to have a sequential number for this kind of data in order to make certain math calculations easier for comparisons between discrete units of time, etc.

Given a table such as this:

Year        WeekNum

2016	48
2016	49
2016	50
2016	51
2016	52
2017	1
2017	2
2017	3
2017	4
2017	5
2017	6
2017	7
2017	8
2017	9
2017	10
2017	11
2017	12
2017	13
2017	14
2017	15
2017	16
2017	17
2017	18
2017	19
2017	20
2017	21
2017	22
2017	23
2017	24
2017	25
2017	26
2017	27
2017	28
2017	29
2017	30
2017	31
2017	32
2017	33
2017	34
2017	35
2017	36
2017	37
2017	38
2017	39
2017	40
2017	41
2017	42
2017	43
2017	44
2017	45
2017	46
2017	47
2017	48
2017	49
2017	50
2017	51
2017	52
2018	1
2018	2
2018	3
2018	4
2018	5
2018	6

A measure can be constructed such as:

Sequential = 
VAR MaxWeeks = SUMMARIZE(ALL('YearWeeks'),'YearWeeks'[Year],"MaxWeek",MAX('YearWeeks'[WeekNum]))
VAR MyYear = MAX('YearWeeks'[Year])
VAR MyStart = SUMX(FILTER(MaxWeeks,[Year]<MyYear),[MaxWeek])
VAR firstYear = CALCULATE(FIRSTNONBLANK('YearWeeks'[Year],1),ALL('YearWeeks'))
VAR myNum = IF(MAX('YearWeeks'[Year])=firstYear,MAX('YearWeeks'[WeekNum]),MyStart+MAX('YearWeeks'[WeekNum]))
RETURN myNum

This provides a sequential number for the Years and Weeks listed in the table.

There is a column form of this as well:

Column = 
VAR MaxWeeks = SUMMARIZE(ALL('YearWeeks'),'YearWeeks'[Year],"MaxWeek",MAX('YearWeeks'[WeekNum]))
VAR MyYear = [Year]
VAR MyStart = SUMX(FILTER(MaxWeeks,[Year]<MyYear),[MaxWeek])
VAR firstYear = CALCULATE(FIRSTNONBLANK('YearWeeks'[Year],1),ALL('YearWeeks'))
VAR myNum = IF(MyYear=firstYear,[WeekNum],MyStart+[WeekNum])
RETURN myNum

eyJrIjoiMTI3N2Y2NWYtMzFjNS00OWUxLWIwNWItNzIwMjE2NzNjZDc3IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9

[powerUserRefine]

Hi Greg,

thank you for sharing.

Measure works great, but when I trying to create custom column I have eof error after first MaxWeeks.
Do you have some idea why it's not working?

Cheers Ivan

[Greg_Deckler]

Sure, are you getting that error with the attached PBIX file? Or your own file?

[powerUserRefine]

Hi Chris, thank you for response.

I use it in my own project. The reason why it is not creating custom column, is that I use direct query data in my project.

I figured out how to use measure instead.

Cheers
Ivan

[Anonymous]

Can you please post how to use measure to create index column using Direct query?

[Anonymous]

Thank you very much! allways nice when the solution is ready!