Calculate Hours for the following week

[ravelrausch]

I have 2 date columns - one shows the start of each main week. the other one shows the start of the next week. 
i have an Hours column which shows the sum of hours against the main weeks. 
Now i need to create the DAX column "NextWeek Hours" which is basically the same as "MainWeek Hours" column but just moved one row up. How do i create the "NextWeek Hours" column DAX based on the existing columns in the table:

Employee	RosterPeriod	MainWeekNumber	MainWeek	MainWeek Hours	NextWeek	NextWeek Hours
662726	07/04/2024 to 04/05/2024	W - 1	7/04/2024	23.3	14/04/2024	22.9
662726	07/04/2024 to 04/05/2024	W - 2	14/04/2024	22.9	21/04/2024	18.9
662726	07/04/2024 to 04/05/2024	W - 3	21/04/2024	18.9	28/04/2024	23.9
662726	07/04/2024 to 04/05/2024	W - 4	28/04/2024	23.9	5/05/2024	23.0
662726	05/05/2024 to 01/06/2024	W - 1	5/05/2024	23.0	12/05/2024	21.2
662726	05/05/2024 to 01/06/2024	W - 2	12/05/2024	21.2	19/05/2024	34.3
662726	05/05/2024 to 01/06/2024	W - 3	19/05/2024	34.3	26/05/2024	30.2
662726	05/05/2024 to 01/06/2024	W - 4	26/05/2024	30.2	2/06/2024	29.3
662726	02/06/2024 to 29/06/2024	W - 1	2/06/2024	29.3	9/06/2024	3.0
662726	02/06/2024 to 29/06/2024	W - 2	9/06/2024	3.0	16/06/2024

[Poojara_D12]

Hi @ravelrausch 

Use the LOOKUPVALUE function in DAX to create the NextWeek Hours column. Here's the formula:

NextWeek Hours =
LOOKUPVALUE(
    TableName[MainWeek Hours],
    TableName[MainWeek],
    TableName[NextWeek]
)

• Matches the NextWeek column to the MainWeek column.
• Returns the corresponding MainWeek Hours value.
• This shifts the hours up by one row to populate NextWeek Hours.

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS

[Anonymous · Accepted solution]

Hi @ravelrausch ,

Kedar_Pande's workaround is a good way which could solve your issue by  LOOKUPVALUE() function.

You can also try my code to create a calculated column.

NextWeek Hours = 
VAR _NEXTWEEK = 'Table'[NextWeek]
RETURN
CALCULATE(SUM('Table'[MainWeek Hours]),FILTER('Table','Table'[MainWeek] = _NEXTWEEK))

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ravelrausch]

Thank you so much! Your query actually solved my issue! Appreciate it!

[sanalytics]

@ravelrausch 
Basically you need to use Earlier concept for creating this column..
I have created a helper column for obtaining your result. Let me know if you want the result without helping column.
Follow below code.

Helping Column = 
RANKX(
    VALUES( 'Table'[MainWeek] ),[MainWeek],,DESC )

Next week Hours = 
CALCULATE(
   SUM( 'Table'[MainWeek Hours] ),
    FILTER( 'Table',
       'Table'[Helping Column] = EARLIER( 'Table'[Helping Column] ) -1
    ) )

 Below result

There is Microsoft documentation and beautiful Matt Alington sir's aricle (https://exceleratorbi.com.au/earlier-vs-earliest-dax/ )for grasping the Earlier Concept.Please go through that.

Let me know if you need pbix file.

Hope this helps.

Regards

sanalytics

[Kedar_Pande]

@ravelrausch 

Create column:

NextWeek Hours = 
LOOKUPVALUE(
TableName[MainWeek Hours], 
TableName[MainWeek], 
TableName[NextWeek]
)

Ensure there are no duplicate MainWeek values in your table, as LOOKUPVALUE requires a unique match.

💡 If this helped, please give Kudos 👍 or mark it as a Solution ✅.
Best regards,
Kedar
🌐 Connect on LinkedIn