GROUPBY or maybe row level context(?) help needed

Hi v-diye-msft 

Thanks for trying to help! The expected outcome is in the 'static table' that is included in the pbix file. I have only done the lines for the 'Third 2019' period so it is only showing the last 10 rows from the other table.

Basically I am after two measures:

1. Total Fees MTD - this should sum all the fees for the month until the 'End of Week' date for each row. For the row 'WeekEnding 9 June 2019' it would return sum of all fees fro 1st-9th June. If the week spans across two months, this should be MTD for the month that just started mid week - so for the first row of the 'Static Table' it shows MTD for June 2019 as the week ends on the 2nd June.

2. The second one is something like a running average of qualified staff ratio for the 'Review Period'. The review period is a 4 month period that always starts on the 1st Feb, 1st June and 1st October - this is reflected in the Calendar table.

The data for this is in the 'hours' table. There are multiple lines for each date as the report si generated daily and it is always added to the master file with the 'documentDate' of the date when the report ran.

What I need to do here is for each of the rows (week ending) take the last day of the week and this will filter the documentDate column in the 'hours' table. This will give me the latest file at the time the week got closed off.

Next is to check for the 'review period' the row falls into and get its start date and the row's weekend date is the end of the period. Then average the 'qualifed staff' column to get the value.

For the first row in the 'static table' there are only two days in the review period with no data so thats why it shows n/a. 

I hope this helps a little bit more! Thanks heaps for trying to help.