Calculate Variance between last two complete weeks

[egrospe17]

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?

[Anonymous · Accepted 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.