Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hello DAX Gods,
I have a visual here that compiles hours worked by each group displayed in a weekly basis. What I'm trying to accomplish is to calculate the variance between the last 2 "complete" weeks. For this example, I would like to calculate the difference between the Week of 3/31/2024 and 3/24/2024 since the current week (4/7/2024) is not yet complete. Just to clarify, on the timesheet table, there are already time entries for the week of 4/7/2024 but since that week is not yet complete, I don't want to use "current week".
How do I write the dax formula for this?
Solved! Go to Solution.
Hi @egrospe17 ,
The Table data is shown below(Today is April 9, 2024 and April 14, 2024 is an unfinished week):
Please follow these steps:
1. Use the following DAX expression to create a measure
Measure =
VAR CurrentDay = TODAY()
VAR ISaWeek = WEEKDAY(CurrentDay,2)
VAR EndDay = IF(ISaWeek = 7,CurrentDay, CurrentDay - ISaWeek)
VAR StartDay = EndDay - 7
VAR _a = CALCULATE(SUM('Table'[Hour]),'Table'[Date] = StartDay)
VAR _b = CALCULATE(SUM('Table'[Hour]),'Table'[Date] = EndDay)
RETURN _b -_a
2.Final output
Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @egrospe17 ,
The Table data is shown below(Today is April 9, 2024 and April 14, 2024 is an unfinished week):
Please follow these steps:
1. Use the following DAX expression to create a measure
Measure =
VAR CurrentDay = TODAY()
VAR ISaWeek = WEEKDAY(CurrentDay,2)
VAR EndDay = IF(ISaWeek = 7,CurrentDay, CurrentDay - ISaWeek)
VAR StartDay = EndDay - 7
VAR _a = CALCULATE(SUM('Table'[Hour]),'Table'[Date] = StartDay)
VAR _b = CALCULATE(SUM('Table'[Hour]),'Table'[Date] = EndDay)
RETURN _b -_a
2.Final output
Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
21 | |
15 | |
14 | |
11 | |
7 |
User | Count |
---|---|
26 | |
24 | |
12 | |
11 | |
10 |