SSAS Calculated Measure for Same Weekday Last Year

Question

I've got a Date dimension and a Fact table containing sales by day. I'm trying to do an easy same period last year calculated measure but I can't get it to work. Using the ParallelPeriod function obviously doesn't work as this is not getting the same weekday for last year and sometimes has no value at all.

I have an extra date attribute (PrevDateKey) on my date dimension pointing to the correct day of last year. Is there any way I could use this in a calculated measure?

There's currently a relationship between the Date Dimension and the Sales Fact.

DATE DIM <-> FACT SALE (DateKey) = (SaleDateKey)

So the same date dimension would also have a PrevDateKey that could be linked to the fact but obviously I don't want to duplicate the Measure group.

(PrevDateKey) = (DateKey)

Any help is appreciated.

Badger1000 Badger1000 · Accepted Answer · 2017-01-18T17:29:08

Sounds to me like you need to use a combination of the Properties function and the StrToMember function in your calculated measure.

Something along the lines of:

CREATE 
 MEMBER CURRENTCUBE.[Measures].[PrevMemberKey] AS
        [Date].[Calendar Date].Currentmember.Properties('PrevDateKey')

CREATE 
  MEMBER CURRENTCUBE.[Measures].[Previous Sales] AS
        (
        STRTOMEMBER('[Date].[Calendar Date].&['+[Measures].[PrevMemberKey]+']'),
        [Measures].[Sales]
        )

You may need to rename 'PrevDateKey' to whatever you've named it in your Cube dimension.

Hope this helps

SSAS Calculated Measure for Same Weekday Last Year

1 Answers