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):


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])