Filter for second entry of week in table

[clock0928]

Hi all,

I am in a tight spot with my report and after multiple attempts I am stuck.

I have a power app that team members use to enter and collect their work stats. The team members enter into the app once a week, however if it is the end of the month (lets say Wednesday is the 31st of January and Friday is the 2nd of February) then they would enter twice, once on Wednesday and again on Friday.

This is because the one week is split across 2 different months. I then connect that data to my power bi report and use that data for tables and graphs.

Week in mention - 

Currently, I have a functioning report that will show for the days at the end of the month (please see layout below) - 

However, the issue that I am having is that my "Work On Hand", "Previous Work On Hand Balance" and "Total Work Completed" are summing for both entries of the week for each team member. For example - 

The current measures are -

Work On Hand

msrWorkOnHand_Tasks = SUM('Work On Hand'[Tasks])

Previous Work On Hand Balance

msrLastWeek_WorkOnHand = 
CALCULATE(
    SUM('Work On Hand'[Tasks]),
        'Calendar'[WeekOfYear] = SELECTEDVALUE('Calendar'[WeekOfYear]) - 1, ALL('Calendar')
    )

Total Work Completed - 

msrStartOfMonth_Counted = SUM('Daily Statistics'[Tasks (Amount)])

From what I have worked out - 

"Previous Work On Hand Balance" is what needs to be changed, however I can't put my finger on how it needs to be changed to only capture the first entry of the week (31/01/2024) and not the week before.

Any assistance would be greatly appreciated! 🙂

[amitchandak]

@clock0928 , Create a column like this in your date/calendar table

Week End Date  =

var _week = [Date] - weekday([Date],2) +7

return

if(_week > eomonth([Date],0) && [Date]<= eomonth([Date],0) , eomonth([Date],0) , _week)

use this visual and add a measure like

previous week= calculate([Your measure], dateadd([Date], -7, Day) )

[clock0928]

msrLastWeek_WorkOnHand

Hi @amitchandak ,

Thank you for that. To clarify (i'm still quite a beginner in this space).

I create a new column in my Calendar table with the below formula:

Week End Date =
    var _week = [Date] - weekday([Date], 2) + 7
    return
        if (_week > eomonth([Date], 0) && [Date] <= eomonth([Date], 0), eomonth([Date], 0), _week)

and then in my Work On Hand table, create a measure that is the below formula:

Previous Week = 
    CALCULATE(
        [Your measure],
        DATEADD([Date], -7, DAY)
    )

I don't quite understand what I put as the "Your measure"?

Is that my old measure "msrLastWeek_WorkOnHand"?