The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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 |
Solved! Go to 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.
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]
)
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
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.
Thank you so much! Your query actually solved my issue! Appreciate it!
@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
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.
User | Count |
---|---|
15 | |
8 | |
6 | |
6 | |
6 |
User | Count |
---|---|
23 | |
14 | |
13 | |
8 | |
8 |