I have tried something to recreate like you wanted. This doesn't require any changes to the data source. Just plain calculations but lots of them.
I have groups my calculations for easy reference. I am attaching the workbook here as well. (attaching as twb and the csv file in case you do not have 2018.3 version)
As you see you need to first filter out the days as required. These are your Dimensions basically returning True or False if they fall within your window (Yest / last 7 days / 8-14 days / last 30 days)
Create a dimension called Day Group so that all of these are stacked
Now to the hard part. Last 7 days include yesterday as well, so to include that value we use LOD. So we create the 4 measures for Last 7 days. Likewise for 30 days (since this includes last 7 days, Day 8-14 days data as well). We need to replace these recalculated numbers only for Last 7 days and 30 days else we need the original measures.. This is what is acquired in the Ultimate Measures.
Please let me know if this helps. If so, please mark this as Correct so that others can make use of this.
Like you mentioned we have to create many calculations but it is always better than duplicating the data since the data volume is very large
Great, Glad it was helpful.. Have a nice day.
One more question, what if I need four more row for month to date ,projected month,month last year ,and projected Yoy change in the above table
... I tried but it is not showing up in the Table....
added calculation for MTD...
then added that in the group field name...
then created an LOD expression to calculate clicks for MTD
then updated in cal clicks(penultimate) and the final cicks (ultimate)
But it is not showing in the table...can you help me to understand why I am not getting the Month to Date Row??