Hello Folks,
I have created a table that summarizes the total sales for the current month by location, experience level, and subscription.
Columns 4 to 8 are total sales are coming from the sales data filtered by the current month. Columns 9-13 are the month-end estimates (total sales divided by days elapsed (CM SD)* total of days in the month (CM KD)).
That’s the schema for this table:
If the a.date Table relationship to CM Estimates is active, it only calculates what was sold on 07/26 which is the max date in the sales report, but I don’t want to calculate only sales on that date (CM) but all the sales for the current month.
And if a delete the relationship, it adds the current month calculations to all months (see the matrix).
This total should only be seen in the current month, in this case, July.
I can’t figure out a way to calculate the current month’s estimates but keep the relationship active to have it summarized correctly in the matrix.
Any ideas?