Nested LODs

Hi all,

first time poster here. 

I've been struggling with this cool albeit not strictly necessary feature I've been working on and would greatly appreciate your help.

To outline the case:

I am working on an SQL (Sales Qualified Lead) timeline, showing a per date number of SQLs achieved and a running total that resets to zero at the start of every month. This can then be filtered across clients, let's say Client 1 through 5.

I have repeatedly attempted to calculate a formula for a reference line that works as such:

Within each month, calculate the max of target by client project (the data is stored so that each SQL acquired will have the target assigned to it for each row, i.e. SQL, then sum this max of target for each client project in the view by the filter.

The issue I have come across is that using exclude on SQL_Date or Day(SQL_Date) seems to be having unexpected results when trying to achieve this goal. Currently, the best version of the formula I have managed to concoct is the following:

{ Include [SQL Month], [CAT], [Client], [SQL Year]:MAX([Target])}

Note that [CAT] i.e. category, and [SQL Year] are largely irrelevant for the time being.

I have also tried variations such as:

{ Include [SQL Month], [CAT], [Client], [SQL Year]:MAX({Exclude [SQL_Date__c]:MAX([Target])})}

and

{Exclude [SQL_Date__c]:MAX{ Include [SQL Month], [CAT], [Client], [SQL Year]:MAX(([Target])})}

The one I am currently using (first formula mentioned) as well as the second one seem to be working as long as SQL_Date is not expanded to include day in the view, but neither formula then applies those monthly values to each day value if the day of date is also included.

I will provide images to more clearly illustrate what I am trying to achieve:

  Any and all help would be greatly appreciated. I've kept the post brief for now in order not to disclose too much or unnecessary information, but let me know if anything else is needed from my part to solve this.