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.
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.