topic Re: Week to Date Cumulative Totals not resetting in DAX Commands and Tips

Week to Date Cumulative Totals not resetting

luccketta — Fri, 05 Jun 2020 12:10:49 GMT

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]) ) )

Re: Week to Date Cumulative Totals not resetting

stevedep — Fri, 05 Jun 2020 20:04:04 GMT

Hi,

How does your data model look like?

Re: Week to Date Cumulative Totals not resetting

Anonymous — Fri, 05 Jun 2020 20:13:49 GMT

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-Last-Week/ba-p/1051123

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)

Re: Week to Date Cumulative Totals not resetting

luccketta — Fri, 05 Jun 2020 22:17:38 GMT

Steve, here is my Data Model.

Re: Week to Date Cumulative Totals not resetting

luccketta — Fri, 05 Jun 2020 22:24:32 GMT

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.

Re: Week to Date Cumulative Totals not resetting

mahoneypat — Sat, 06 Jun 2020 00:44:41 GMT

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

Re: Week to Date Cumulative Totals not resetting

luccketta — Sat, 06 Jun 2020 01:07:59 GMT

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! 🙂