GROUPBY or maybe row level context(?) help needed

[4-3-3]

Hey guys, sorry about the confusing title but I am pretty lost with this one!

I am trying to put together a week-on-week table with fees and staff ratios but I am not sure how to include a column for running total of month-to-date and reviewperiod-to-date (quadrimestre).

The screenshot above is showing the extra two columns in the 'static table' on the right. The month-to-date fees are showing the fees for the MTD of the last day of the week - e.g. week ending on 2nd June is only showing the first two days of June.

The Staff ratio for 'period-to-date' is even worse and actually hurting my brain:) The source data has multiple lines for each date and it should always take the date line with the latest 'documentDate' column value. I thought I would just filter the MAX(date) of the 'documentDate' column but that one wouldnt have all the date lines included..

This one takes the weekEnd date as the 'documentDate' from the 'hours' table and averages all the 'Qualified Staff' ratios. E.g. for week ending 23/06/19 it filters the documentDate as 23/06/19 and averages all ratios between 01/06/19 (period 'Third 2019' start) and 23/06/19. 

I have uploaded the pbix file here.

Appreciate your help!

[v-diye-msft]

Hi @4-3-3 ，

Sorry your explanation above makes me a little bit mixed. Could you please just let us know your expected result? it's preferred to list your simple logic and i'd like to provide the further advice.

[4-3-3]

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.