Forum Discussion

andrehawari's avatar
andrehawari
Helper II
7 years ago
Solved

Next Week Function

I need a measure to calculate next week value. I already formated the data model that only include row in weekly level.  The next week value should be accomodated by two slicer: Filter 1 and Filter2...
Need Help
  • themistoklis's avatar
    themistoklis
    7 years ago

    Slightly amended the formula to take into account the first week as well

     

    next_week = CALCULATE(
SUM(Sheet1[Value]),
	FILTER(ALL(Sheet1), IF(WEEKNUM(Sheet1[Date], 21)=51,0,WEEKNUM(Sheet1[Date], 21)) = IF(WEEKNUM(MAX(Sheet1[Date]), 21)=51,0,WEEKNUM(MAX(Sheet1[Date]), 21)) + 1
    && Sheet1[Filter1] = MAX(Sheet1[Filter1])
    && Sheet1[Filter2] = MAX(Sheet1[Filter2])
	))
themistoklis's avatar
themistoklis
Community Champion
7 years ago

Slightly amended the formula to take into account the first week as well

 

next_week = CALCULATE(
SUM(Sheet1[Value]),
	FILTER(ALL(Sheet1), IF(WEEKNUM(Sheet1[Date], 21)=51,0,WEEKNUM(Sheet1[Date], 21)) = IF(WEEKNUM(MAX(Sheet1[Date]), 21)=51,0,WEEKNUM(MAX(Sheet1[Date]), 21)) + 1
    && Sheet1[Filter1] = MAX(Sheet1[Filter1])
    && Sheet1[Filter2] = MAX(Sheet1[Filter2])
	))