topic Re: Return the value for the next succeeding week in DAX Commands and Tips

Return the value for the next succeeding week

ironboy0423 — Thu, 27 Jul 2023 09:40:17 GMT

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

)

Re: Return the value for the next succeeding week

eliasayyy — Thu, 27 Jul 2023 10:01:43 GMT

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)

Re: Return the value for the next succeeding week

ironboy0423 — Thu, 27 Jul 2023 10:59:30 GMT

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)

Re: Return the value for the next succeeding week

ironboy0423 — Thu, 27 Jul 2023 21:43:42 GMT

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

Re: Return the value for the next succeeding week

eliasayyy — Fri, 28 Jul 2023 04:13:04 GMT

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)

Re: Return the value for the next succeeding week

ironboy0423 — Fri, 28 Jul 2023 05:07:29 GMT

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

Re: Return the value for the next succeeding week

ironboy0423 — Fri, 28 Jul 2023 05:35:20 GMT

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

Re: Return the value for the next succeeding week

eliasayyy — Fri, 28 Jul 2023 05:53:26 GMT

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

Re: Return the value for the next succeeding week

ironboy0423 — Tue, 08 Aug 2023 23:59:17 GMT

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)