I don't understand the question.
Can you upload a sample workbook so I can see it firsthand?
That kind of helps. But I still don't understand what you're trying to do.
Your example seems to have set the "filter" to TODAY(). That would be September. How do you know when to dig into August?
If you really meant that the user entered an August date, How do you know to dig into September?
Also, I asked for a workbook because I need to see how you are applying the filter to your sheet. Is it realty a filter? Or is it a parameter. (And please, I'm not asking you just to answer that question. I really need a workbook here.)
I can't provide a workbook due to confidentiality reasons but the filter is set up by applying a relative date filter last 7 days based on [Date]. Then I applied another [Date] filter on MDY and used that MDY filter on my dashboard. That way the user can only go back 7 days and see data from those specific days. No parameters used. That MDY dashboard filter is applied to the Daily Summary number but not the MTD Summary as I'm not sure what is needed there.
The today() was never used but would be used if I was simply trying to calculate the MTD data for the current month, in which case this question wouldn't be asked. If it was mid-month, the user would never see the previous month dates on the slider since it only goes back 7 days. But in the early month, if the latest date is before the 7th, it will go back into the previous month as well on the slider.
How do I account for this for the MTD view? For MTD view I want to see the MTD number based on whatever month the user has the MDY year filtered on. For example, let's say the MDY filter is filtered on Sept 27 and today's date is 30th - the daily summary shows only that day's data but the MTD will show Sept 1 - 30. If today was 29th, it would show Sept 1-29.
Another example, Sept 3 is selected on slider and current date is Sept 5. We'd see Sept 3 data for daily summary, but Sept 1-5 data for MTD. The user then scrolls back to August 31. We'd see data for August 31 under daily summary but MTD summary will show data from Aug 1-31.
Don't let proprietary data get in the way:
Here's the thing. You know your application and goals and requirements inside and out. Your description makes perfect sense to you.
I'm coming in from the outside. I can't wrap my head around the description.
If I were doing something like (I think) you are describing, I would give the user a parameter to specify the target date, and then make all my calcs based on that parameter value. You would be able to apply (or not) that parameter to various calcs and display whatever is necessary.