How to calculate Last day of previous week and last day of current week in ssas Cube?

Question

I require to calculate the Last day of previous week and last of day current week in Multidimensional Cube.I need to have in Cube calculation

For example: I have Time dimension with Hierarchies as below

Year-Quarter-month-date
Year-Quarter-month
Year-Quarter-week
Year-Week.

I have Time Week dimension- which has hierarchy Time-week and my View show below data

Week-2018-W18       
WeekNumberOfYear-2018-W18       
Year-18 
YearNo-2018 
StartDate-2018-04-30 00:00:00.000   
EndDate-2018-05-06 23:59:59.997 
CurrentWeek-1

Current Week is set flag set. As per above data.

If I run the report today(03/05/2018)-Thursday.

previous week last working day should show- 2018-04-27- Friday

Current week last day should be-2018-05-04-Friday.

Please let me know how to calculate the dates via cube calculation, I am able to find the previous year with Parallel Period function.

score 0 · Answer 1 · edited Jun 12 '18 at 06:37

0

First create the function in DB and try this.

CREATE  Function Lynk_Current_WeekStartDate
(
@processDate datetime
)
Returns DateTime
As
Begin 

    Declare @FromDate DateTime 

    Set @FromDate=dateadd(d,0,dateadd("ww",datediff(wk,'01/01/1900',DATEADD(dd,-1,@processDate)),'01/01/1900')) --Monday
    Return(@FromDate)

End 

CREATE Function Lynk_Current_WeekEndDate
(
@Processdate datetime
)
Returns DateTime
As
Begin 
    Declare @FromDate DateTime 
    Declare @ToDate DateTime 

    Set @FromDate=dbo.Lynk_Current_WeekStartDate(@Processdate)
    Set @Todate=dateadd(ss,-1,dateadd(d,5,@FromDate))
Return(@ToDate)

End

Example:

select dbo.Lynk_Current_WeekStartDate('2018-05-03')
select dbo.Lynk_Current_WeekEndDate('2018-05-03')

edited Jun 12 '18 at 06:37

Pang

9,564
146
81
122

answered May 03 '18 at 09:57

Selvakumar M

1
2

Hi, thanks for sharing, but I need to write as cube calculation. – Sandhya Balakrishnan May 03 '18 at 10:05
Do we have any function like parallel period to find out the previous week last day? – Sandhya Balakrishnan May 03 '18 at 10:14
share the query details. – Selvakumar M May 03 '18 at 10:30
([Time Calculation].[R3 Pr Yr]= Sum( CrossJoin( {[Time Calculation].[Current Period]}, LastPeriods(3, ParallelPeriod( [Time Day].[Year - Quarter - Month].[Year],1, [Time Day].[Year - Quarter - Month].CurrentMember ) ) ) ) – Sandhya Balakrishnan May 03 '18 at 11:06

score 0 · Answer 2 · edited Jun 12 '18 at 06:35

This will work. You need to just mention last day name.

Declare @Pre_last_Dayname datetime, @cur_last_Dayname datetime,  @date datetime,  @found datetime, @dayname varchar(14)
set @date = GETDATE()
set @dayname  = 'Friday' 


WHILE @Pre_last_Dayname is null
BEGIN  
    set @date  = @date - 1
    select @Pre_last_Dayname  =  @date 
    where   DATENAME(dw,@date) = @dayname 

END  ;

set @date = GETDATE()

WHILE @cur_last_Dayname is null
BEGIN  

    select @cur_last_Dayname  =  @date 
    where   DATENAME(dw,@date) = @dayname 
    set @date  = @date + 1
END  

select  concat(format(@Pre_last_Dayname ,'dd/MM/yyyy'),'-',DATENAME(dw,@Pre_last_Dayname) )'last_day of_last_week'
        , concat(format(@cur_last_Dayname ,'dd/MM/yyyy'),'-',DATENAME(dw,@cur_last_Dayname) )'last_day of_current_week' 
        , concat(format(GETDATE() ,'dd/MM/yyyy'),'-',DATENAME(dw,GETDATE()) )'current_date'

Hi, Thank you for the response, But I need in cube calculation, for example I am able to pull last year with Parallel period function. — Sandhya Balakrishnan, May 03 '18 at 10:10

score 0 · Answer 3 · edited Jun 12 '18 at 06:36

Use this for getting previous week date.

Declare @CurWkFromDate      DateTime,
        @CurWkTodate        DateTime,
        @PrvWkFromdate      DateTime,
        @PrvWkTodate        DateTime,
        @Ondate             DateTime    = '2018-05-03'

select @CurWkFromDate   = dbo.Lynk_Current_WeekStartDate(@Ondate),
       @CurWkTodate     = dbo.Lynk_Current_WeekEndDate(@Ondate),
 @PrvWkFromdate = dbo.Lynk_Current_WeekStartDate(Dateadd(dd,-1,@CurWkFromDate)),
       @PrvWkTodate     = dbo.Lynk_Current_WeekEndDate(@PrvWkFromdate)
       select @CurWkFromDate,@CurWkTodate,@PrvWkFromdate,@PrvWkTodate

How to calculate Last day of previous week and last day of current week in ssas Cube?

I require to calculate the Last day of previous week and last of day current week in Multidimensional Cube.I need to have in Cube calculation

3 Answers3