Week To Date Measure

From DataSelf Knowledge Base
Jump to navigation Jump to search

Unclear:: In each sub-section, Is the code for DS ETL, SSAS, or SQL (SQL Server)?

Week To Date Measure

For a WTD that starts with Monday and does not depend on a Week level in the time dimension.

In SYS_Calendar make formula (Sunday = 1):

Day-0f-the-Week_Formula.JPG

We will use this number to determine how many days back to go when summing up the current week.

But this client wanted the week to start on Monday.

1. Make a field in the _T_DateDocument view:

CASE WHEN [Day-of-the-week] = 1 THEN 6 ELSE [Day-of-the-week] - 2 END AS WTD_LagDays (This is how many days to count back, not counting today, to get to last Monday.)

2. Make the WTD_LagDays a Property of the Date attribute (not sure if this still will work this way in SQL 2005 +)

3. Then make a calculated measure:


sum(([Order Date].currentmember.lag(StrToValue([Order Date].currentmember.Properties("Wtd Lag Days"))):[Order Date].currentmember),[Measures].[Amt Sold]) Or, to see it split out into its logic:

sum( adding up a range of days ( [Order Date].currentmember.lag( First part of a range, lag to count back enough days to go back to Monday StrToValue([Order Date].currentmember.Properties("Wtd Lag Days"))) Convert the Property to a numeric

the Range operator

[Order Date].currentmember the other end of the range, so it includes all days from Monday to this date ) ,[Measures].[Amt Sold] )

(This was created in SQL 2000, so the date dimension references will need to be changed for SQL 2005 and later. And I don’t know if you would still need to make the Wtd_LagDays a property, or just reference it as another attribute.)

Other Date Formulas

Sum( MTD([Date Document].[Date Document]), [Measures].[Amt Gross Profit] )

Sum( QTD([Date Document].[Date Document]), [Measures].[Amt Gross Profit] )

Sum( YTD([Date Document].[Date Document]), [Measures].[Amt Gross Profit] )

Sum( YTD(ParallelPeriod([Date Document].[Date Document].[Year], 1)), [Measures].[Amt Gross Profit] )

Same Period Prior Year (ParallelPeriod([Date Document].[Date Document].[Year], 1,[Date Document].[Date Document].currentmember), [Measures].[Amt Sales])