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

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