topic Re: Calculate Hours for the following week in DAX Commands and Tips

Calculate Hours for the following week

Anonymous — Fri, 27 Dec 2024 05:18:18 GMT

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

Re: Calculate Hours for the following week

Kedar_Pande — Fri, 27 Dec 2024 05:41:05 GMT

Anonymous

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

Re: Calculate Hours for the following week

sanalytics — Fri, 27 Dec 2024 08:18:07 GMT

Anonymous
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

Re: Calculate Hours for the following week

Anonymous — Mon, 30 Dec 2024 04:01:10 GMT

Hi Anonymous ,

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.

Re: Calculate Hours for the following week

Poojara_D12 — Mon, 30 Dec 2024 07:08:47 GMT

Hi Anonymous

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

Re: Calculate Hours for the following week

Anonymous — Tue, 31 Dec 2024 00:05:31 GMT

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