This must be added inside the cube, need to change hardcoded hierarchies.
--get last sunday
CREATE MEMBER CURRENTCUBE.[Measures].[CurrentWeekT] AS
"[Date].[Year - Season - Quarter - Period - Week - Date].[Week].&[" +
FORMAT(VBA!DateAdd("d", -VBA!WeekDay(VBA!Now()) + 1, VBA!Now()),"yyyy") +
"-" +
FORMAT(VBA!DateAdd("d", -VBA!WeekDay(VBA!Now()) + 1, VBA!Now()),"MM") +
"-" +
FORMAT(VBA!DateAdd("d", -VBA!WeekDay(VBA!Now()) + 1, VBA!Now()),"dd") +
"T00:00:00]", VISIBLE = 0;
--dynamic set to get current date
CREATE HIDDEN DYNAMIC SET CURRENTCUBE.[SET_CurrentDateT] AS
STRTOSET("[Date].[Date].&[" +
FORMAT(VBA!Now(),"yyyy") + "-" + FORMAT(VBA!Now(),"MM") + "-" + FORMAT(VBA!Now(),"dd") +
"T00:00:00]");
--dynamic set to get current week last
CREATE HIDDEN DYNAMIC SET CURRENTCUBE.[SET_CurrentWeekT] AS
STRTOSET("[Date].[Year - Week].[Year Week].&[" +
FORMAT(VBA!DateAdd("d", -VBA!WeekDay(VBA!Now()) + 1, VBA!Now()),"yyyy") +
"-" +
FORMAT(VBA!DateAdd("d", -VBA!WeekDay(VBA!Now()) + 1, VBA!Now()),"MM") +
"-" +
FORMAT(VBA!DateAdd("d", -VBA!WeekDay(VBA!Now()) + 1, VBA!Now()),"dd") +
"T00:00:00]");
--
//Current Week -1
( [Date Calcs].[Comparison].&[6],
[Date Calcs].[Aggregation].Members )
= ( [Date Calcs].[Comparison].DefaultMember,
ParallelPeriod([Date].[Year - Season - Quarter - Period - Week - Date].[Week],
0,
STRTOMEMBER([CurrentWeekT]) )
);
//Current Week -2
( [Date Calcs].[Comparison].&[7],
[Date Calcs].[Aggregation].Members )
= ( [Date Calcs].[Comparison].DefaultMember,
ParallelPeriod([Date].[Year - Season - Quarter - Period - Week - Date].[Week],
1,
STRTOMEMBER([CurrentWeekT]) )
);
//Current Week -3
( [Date Calcs].[Comparison].&[8],
[Date Calcs].[Aggregation].Members )
= ( [Date Calcs].[Comparison].DefaultMember,
ParallelPeriod([Date].[Year - Season - Quarter - Period - Week - Date].[Week],
2,
STRTOMEMBER([CurrentWeekT]) )
);
//Current Week -4
( [Date Calcs].[Comparison].&[8],
[Date Calcs].[Aggregation].Members )
= ( [Date Calcs].[Comparison].DefaultMember,
ParallelPeriod([Date].[Year - Season - Quarter - Period - Week - Date].[Week],
3,
STRTOMEMBER([CurrentWeekT]) )
);
//Current Week -5
( [Date Calcs].[Comparison].&[8],
[Date Calcs].[Aggregation].Members )
= ( [Date Calcs].[Comparison].DefaultMember,
ParallelPeriod([Date].[Year - Season - Quarter - Period - Week - Date].[Week],
4,
STRTOMEMBER([CurrentWeekT]) )
);
--get last sunday
CREATE MEMBER CURRENTCUBE.[Measures].[CurrentWeekT] AS
"[Date].[Year - Season - Quarter - Period - Week - Date].[Week].&[" +
FORMAT(VBA!DateAdd("d", -VBA!WeekDay(VBA!Now()) + 1, VBA!Now()),"yyyy") +
"-" +
FORMAT(VBA!DateAdd("d", -VBA!WeekDay(VBA!Now()) + 1, VBA!Now()),"MM") +
"-" +
FORMAT(VBA!DateAdd("d", -VBA!WeekDay(VBA!Now()) + 1, VBA!Now()),"dd") +
"T00:00:00]", VISIBLE = 0;
--dynamic set to get current date
CREATE HIDDEN DYNAMIC SET CURRENTCUBE.[SET_CurrentDateT] AS
STRTOSET("[Date].[Date].&[" +
FORMAT(VBA!Now(),"yyyy") + "-" + FORMAT(VBA!Now(),"MM") + "-" + FORMAT(VBA!Now(),"dd") +
"T00:00:00]");
--dynamic set to get current week last
CREATE HIDDEN DYNAMIC SET CURRENTCUBE.[SET_CurrentWeekT] AS
STRTOSET("[Date].[Year - Week].[Year Week].&[" +
FORMAT(VBA!DateAdd("d", -VBA!WeekDay(VBA!Now()) + 1, VBA!Now()),"yyyy") +
"-" +
FORMAT(VBA!DateAdd("d", -VBA!WeekDay(VBA!Now()) + 1, VBA!Now()),"MM") +
"-" +
FORMAT(VBA!DateAdd("d", -VBA!WeekDay(VBA!Now()) + 1, VBA!Now()),"dd") +
"T00:00:00]");
--
//Current Week -1
( [Date Calcs].[Comparison].&[6],
[Date Calcs].[Aggregation].Members )
= ( [Date Calcs].[Comparison].DefaultMember,
ParallelPeriod([Date].[Year - Season - Quarter - Period - Week - Date].[Week],
0,
STRTOMEMBER([CurrentWeekT]) )
);
//Current Week -2
( [Date Calcs].[Comparison].&[7],
[Date Calcs].[Aggregation].Members )
= ( [Date Calcs].[Comparison].DefaultMember,
ParallelPeriod([Date].[Year - Season - Quarter - Period - Week - Date].[Week],
1,
STRTOMEMBER([CurrentWeekT]) )
);
//Current Week -3
( [Date Calcs].[Comparison].&[8],
[Date Calcs].[Aggregation].Members )
= ( [Date Calcs].[Comparison].DefaultMember,
ParallelPeriod([Date].[Year - Season - Quarter - Period - Week - Date].[Week],
2,
STRTOMEMBER([CurrentWeekT]) )
);
//Current Week -4
( [Date Calcs].[Comparison].&[8],
[Date Calcs].[Aggregation].Members )
= ( [Date Calcs].[Comparison].DefaultMember,
ParallelPeriod([Date].[Year - Season - Quarter - Period - Week - Date].[Week],
3,
STRTOMEMBER([CurrentWeekT]) )
);
//Current Week -5
( [Date Calcs].[Comparison].&[8],
[Date Calcs].[Aggregation].Members )
= ( [Date Calcs].[Comparison].DefaultMember,
ParallelPeriod([Date].[Year - Season - Quarter - Period - Week - Date].[Week],
4,
STRTOMEMBER([CurrentWeekT]) )
);