cancel
Showing results for 
Search instead for 
Did you mean: 

Sequential

Super User
2010 Views
Highlighted
Super User
Super User

Sequential

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

 

 


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Frivik Regular Visitor
Regular Visitor

Re: Sequential

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