Sequential

Greg_Deckler — Wed, 27 Jun 2018 17:21:25 GMT

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

Re: Sequential

Anonymous — Wed, 12 Dec 2018 21:18:38 GMT

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

Re: Sequential

powerUserRefine — Tue, 07 Apr 2020 09:14:59 GMT

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

Re: Sequential

Greg_Deckler — Tue, 07 Apr 2020 14:14:33 GMT

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

Re: Sequential

powerUserRefine — Wed, 15 Apr 2020 08:30:54 GMT

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

Re: Sequential

Anonymous — Thu, 28 Jan 2021 23:37:27 GMT

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

topic Re: Sequential in Quick Measures Gallery

Sequential

Re: Sequential

Re: Sequential

Re: Sequential

Re: Sequential

Re: Sequential