cancel
Showing results for 
Search instead for 
Did you mean: 

Sequential

Super User IV
3829 Views
Super User IV
Super User IV

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

 

 


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Anonymous
Not applicable

Re: Sequential

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

 

 

powerUserRefine
New Member

Re: Sequential

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

Highlighted
Super User IV
Super User IV

Re: Sequential

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

---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

powerUserRefine
New Member

Re: Sequential

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