Week to Date Cumulative Totals not resetting

[luccketta]

Hello,

I have a measure that is attempting to return total hours per week. My slicer is filtering on pay period which is bi-weekly. The total is working fine for the first week in the pay period however it is not resetting to zero when it gets to the second week of the pay period but instead continues the cumulative. I'm guessing it may have something to do with the pay period coming from my DATA table rather than my DATE table but I can't be sure.

Thanks for your guidance!

WE Total Hours = 
CALCULATE(
	[Hours],
	FILTER(
		ALLSELECTED('Dates'[Date]),
		'Dates'[Date] >= (Dates[Date] - WEEKDAY('Dates'[Date],2)) && 
        'Dates'[Date] <= MAX('Dates'[Date])
    )
)

[mahoneypat]

The second term in your Filter ( the MAX expression) is going to the end of the two-week period I believe.  Make your second comparison like the first so it calculated dynamically.

LDates[Date]+7-WEEKDAY(Dates[Date],2)  // or maybe +6 but you get the idea

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[luccketta]

OK, so close... It was the minimum date causing the error. You got me on the right track and I came up with this:

WE Total Hours = 
CALCULATE(
	[Hours],
	FILTER(
		ALLSELECTED('Dates'[Date]),
		IF(DATEDIFF((Dates[Date] - WEEKDAY('Dates'[Date],2)),MAX(Dates[Date]),DAY)>7,
            'Dates'[Date] >= (7 + Dates[Date] - WEEKDAY('Dates'[Date],2)), 
            'Dates'[Date] >= (Dates[Date] - WEEKDAY('Dates'[Date],2))) && 
        'Dates'[Date] <= MAX(Dates[Date])
    )
)

Result is:

The world is right again! 🙂

[harshnathani]

Hi @luccketta ,

See this blog on Weeks.

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

also Weekday returns an integer, so in the formula you are subtracting a date and an integer.

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[luccketta]

Harsh, thanks for your reply!

"Weekday returns an integer, so in the formula you are subtracting a date and an integer", 

I was trying to set the minimum date to the start of the week (Monday) of the filter context.

So I can get total hours for the current week of the row shown Monday through Sunday.

The blog has good stuff but I can't figure how the RANK will get me to the finish line.

[stevedep]

Hi,

How does your data model look like?

[luccketta]

Steve, here is my Data Model.