Your data is on a cube, so the tbwx above does not have it (The worksheet "Profit by Month" has not been opened because it is based on the data source "Gross Profit Cube (GrossProfitCube)", which could not be opened.)
However, from your prior question, we know the total days in a month:
We can calculate the number of days into the month:
so the ratio is datepart('day',today()) / datediff('day',datetrunc('month',[Date]),dateadd('month',1,datetrunc('month',[Date])))
We can check if the current row is in the current month
if datetrunc('month',today())=datetrunc('month',[Date]) then .....
Now the problem lies in aggregation - I don't know how your data is structured (one row per month that gets updated daily, or multiple rows per month (per hour, day...) that we need to somehow roll up
Note - this does assume that the data is added daily. If that isn't the case then we would need some other datestamp to get the update day.
We may have to get complicated with table calcs....
Thank you very much for the quick follow up. However, I have since been called upon to put out a fire. I will get back to you next week.
Your formula's were great. My biggest struggle was getting the "sale date" to be part of the formula. When I accessed the cube through Analysis Services, I could not utilize the "sale date" ( I am assuming because of hierarchy issues). When I connected to the cube directly through the sql server it worked. - Trial and Error.
Thank you very much.