I would like to add a calculation to provide an annualized Gross Amount value. Most solutions I have seen are for annualizing down to the day. For instance, if today is the 123rd day of the year, the calculation would be Amount / 123 * 365. What I want to do is annualize the amount based only on completed periods. Today is 8/26. The last completed quarter ended on 6/30. Therefore I want to basically take the sum of Q1 and Q2, divide by two, and multiply by four to get an annualized amount based on completed quarters. Eventually I want to do something similar with months.
So here's what I have so far. In my DSV my DimDate is actually based on a view. The view has a Date column with the actual date, all of the normal fields you would expect in a DimDate table, and two columns that look like this:
CASE WHEN [Date] < DATEADD(QQ, DATEDIFF(QQ, 0, GETDATE()), 0) THEN 'Y' ELSE 'N' END AS CompletedQuarter, DATEPART(QQ, DATEADD(QQ, DATEDIFF(QQ, 0, [Date]), 0)) - 1 AS CompletedQuarterCount
The idea is to provide an indicator of whether the current date is in a completed quarter or not, and also the number of completed quarters for the year.
So if today is 8/26/15, you would see these view:
SELECT [Date], CompletedQuarter, CompletedQuarterCount
FROM dbo.vwDimDate
WHERE DateDWKey IN (20150630, 20150701)
2015-06-30 Y 1
2015-07-01 N 2
Note that for dates between 1/1 and 3/31 the CompletedQuarterCount will be 0. I want any annualized amount to be 0 in that case because I only want to use completed quarters in my calculation.
I added both to my Pay Date dimension (which uses vwDimDate from the DSV) in the cube. I have tried the calculation below and in my Excel pivot table I'm getting blanks for the calculated field.
CREATE MEMBER CURRENTCUBE.[Measures].[Gross Amount Annualized by Pay Date Quarter] AS SUM( IIF([Pay Date].[Pay Date Completed Quarter Count] > 0, [Gross Amount] / [Pay Date].[Pay Date Completed Quarter Count] * 4, 0) ), FORMAT_STRING = "Currency", VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Fact Claim' ;
So first, is my calculation correct? And second, am I maybe just going about this wrong?
Thanks!
- Edited by mateoc15 13 hours 3 minutes ago