Return the value for the next succeeding week

[ironboy0423]

Hi guys, I am trying to create a dax measure that will return the value of hours for the succeeding week. I already have the correct dax for Week 1, however I am failing to get the correct value for week 2. Here's what I have so far:

Week2DurationHours =

VAR SelectedWeekStart = SELECTEDVALUE('01_Date'[Start of Week])

VAR Week2StartDate = CALCULATE(MIN('01_Date'[CalendarDate]), '01_Date'[Start of Week] = SelectedWeekStart) + 7

VAR Week2EndDate = Week2StartDate + 6

RETURN

    CALCULATE(

        SUM('TimeEntry'[DurationHours]),

        'TimeEntry'[EntryDate] >= Week2StartDate && 'TimeEntry'[EntryDate] <= Week2EndDate

    )

[ironboy0423]

I'm not sure wich dates covers the returned values, but it is definitely inaccurate so far, may I know your suggestion to this?

[eliasayyy]

hello if you haev  salicer, then you need to add the remove filter on the week 2 

instead of those you can create a week ID in your calendar table 

Week ID = 
VAR _min = YEAR(MIN(Calendar[Date]))
RETURN
//Choose WEEKNUM(Calendar[Date],2) for week start on monday or WEEKNUM(Calendar[Date],1) for week start on Sunday//
WEEKNUM(Calendar[Date],2) + (YEAR(Calendar[Date]) - _min) *53

now insert your filter slicer on the table ltes say you chose the start of week or week nb 

use the measure

Week2DurationHours =
CALCULTAE(SUM('TimeEntry'[DurationHours]),Remove Filters(Date[your slicer]),Date[Week ID] = MAX(Date[Week ID]) - 1)

[ironboy0423]

Hello, thanks for this. I think we are close - it only returns incorrect values. Here's the dax so far:

Week ID =

VAR _min = YEAR(MIN('01_Date'[CalendarDate]))

RETURN

WEEKNUM('01_Date'[CalendarDate],2) + (YEAR('01_Date'[CalendarDate]) - _min) *53

Week2DurationHours = CALCULATE(SUM('TimeEntry'[DurationHours]),REMOVEFILTERS('01_Date'[Start of Week]), '01_Date'[Week ID] = MAX('01_Date'[Week ID]) - 1)

[eliasayyy]

Ok did you make week id as a calculated column in your calendar , or measure? It needs to be a calculated column

And can you please show me more results as what you are getting ? Are there any other date filters?  And i just reaf it again you want the next week not previous week? Then instead of -1 

Week2DurationHours = CALCULATE(SUM('TimeEntry'[DurationHours]),REMOVEFILTERS('01_Date'[Start of Week]), '01_Date'[Week ID] = MAX('01_Date'[Week ID]) + 1)

[ironboy0423]

Thanks again. I update the dax to +1 and it works! thank you so much

[eliasayyy · Accepted solution]

Glad it worked sorry again for not reading well if you can please accept this post as solultion to help other people know its been solved

[ironboy0423]

Hello, sorry for the delay. I notice that our dax for week2duration returns all the sum of a row if there's no succeeding week ahead on a table. How can we fix it please? resharing the dax: 

ReckonWeek2DurationHours = CALCULATE(SUM('Table1'[Duration]), REMOVEFILTERS('01_Date'[Start of Week]), '01_Date'[Week ID] = MAX('01_Date'[Week ID]) + 1)

[ironboy0423]

Hello, thanks for your reply. Here's my response:

Ok did you make week id as a calculated column in your calendar , or measure? It needs to be a calculated column - Yup created a calculated in 'Date' table and measure in TimeEntry table. 

And can you please show me more results as what you are getting? - Its just random hours, not sure how to check as they filtered using a slicer. Are there any other date filters? None And i just reaf it again you want the next week not previous week? Then instead of -1  - Yup, next week please