Forum Discussion

jaco1951's avatar
jaco1951
Icon for Helper III rankHelper III
8 years ago
Solved

create index in calendar

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)))

 

Show and Tell
  • Anonymous's avatar
    Anonymous
    8 years ago

    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

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    8 years ago

    How about a calculated column like:

     

    Column = CONCATENATE(YEAR([Date]),FORMAT(WEEKNUM([date]),"00"))
  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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