Labels

ASP.NET (1) Data Model (1) Django (1) MDX (15) Python (3) Redshift (3) SSAS (15) SSRS (3) T-SQL (29)

Tuesday, 12 June 2012

To get Sales of Current Year, Previous Year, etc using Lag anf Lead functions


Current Year Sales:

SELECT [Measures].[Sales (000's)] on ROws,
{CROSSJOIN([Date]. [Fiscal Months].[Fiscal Months].Allmembers,STRTOMEMBER("[Date].[Financial Period].[Year].&["+ CASE WHEN
CINT(FORMAT(NOW(),"MM")) >= 4 THEN FORMAT(NOW(),"yyyy") ELSE  CSTR(CINT(FORMAT(NOW(),"yyyy"))-1) END +"]"))}
on Columns
FROM [Sales]

Result






Previous year Sales

SELECT [Measures].[Sales (000's)] on ROws,
{CROSSJOIN([Date]. [Fiscal Months].[Fiscal Months].Allmembers,STRTOMEMBER("[Date].[Financial Period].[Year].&["+ CASE WHEN
CINT(FORMAT(NOW(),"MM")) >= 4 THEN FORMAT(NOW(),"yyyy") ELSE  CSTR(CINT(FORMAT(NOW(),"yyyy"))-1) END +"]").lag(1))}on Columns
FROM [Sales]

SELECT [Measures].[Sales (000's)] on ROws,
{CROSSJOIN([Date].[Fiscal Months].[Fiscal Months].Allmembers,STRTOMEMBER("[Date].[Financial Period]. [Year].&["+ CASE WHEN
CINT(FORMAT(NOW(),"MM")) >= 4 THEN FORMAT(NOW(),"yyyy") ELSE  CSTR(CINT(FORMAT(NOW(),"yyyy"))-1) END +"]").lead(-1))}
on Columns
FROM [Sales]

Result


No comments:

Post a Comment