Forum Discussion
Calculate Variance between last two complete weeks
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?
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 -_a2.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.
1 Reply
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 -_a2.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.