Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
egrospe17
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?

 

egrospe17_0-1712618197166.png

 

1 ACCEPTED SOLUTION
v-zhouwen-msft
Community Support
Community Support

Hi @egrospe17 ,

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

vzhouwenmsft_0-1712629162621.png

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

vzhouwenmsft_1-1712629335543.png

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.

View solution in original post

1 REPLY 1
v-zhouwen-msft
Community Support
Community Support

Hi @egrospe17 ,

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

vzhouwenmsft_0-1712629162621.png

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

vzhouwenmsft_1-1712629335543.png

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.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.