cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## 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?

1 ACCEPTED SOLUTION
Community Support

Hi @egrospe17 ,

The Table data is shown below(Today is April 9, 2024 and April 14, 2024 is an unfinished week):

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.

Community Support

Hi @egrospe17 ,

The Table data is shown below(Today is April 9, 2024 and April 14, 2024 is an unfinished week):

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.

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors