create index in calendar

[jaco1951]

Hi all

I try to figure out how to sum values that are reported in week X as a forecast for week X+3

I think an Index in the calendar based on YearWeek could solve it, but I cannot figure out how to create such. 

This formula here does work. But it will not work when my yearweek is 201752, as the formula will return 201755...

Any suggestion to how I can solve this?

amount_startBal = CALCULATE(SUMX(vwDETAILS;vwDETAILS[amount]);
			FILTER(cal_Forfallsdatoer;cal_Forfallsdatoer[ForfallYearWeek] = 
					CALCULATE(MAX(cal_ReportedDates[ReportedYearWeek])+3)))

[Anonymous · Accepted solution]

Hi @jaco1951,

I don't think you can direct use 'current week number +3' to find out the specific records. (You will face year conversion issue)

In my opinion, I'd like to suggest you calculate on original date(+21) to find out corrsponed date, then use result to find out the correct year week.

Regards,

Xiaoxin Sheng

[Greg_Deckler]

How about a calculated column like:

Column = CONCATENATE(YEAR([Date]),FORMAT(WEEKNUM([date]),"00"))