Prior Week Calculation with Segments

[Tihannah]

My Prior Week Calculation is working fine... until I bring segments in.

This is my dax:

Prior Wk Sales = CALCULATE(SUM(Table[Sales]),FILTER(ALL(Date Table),Date Table[WeekRank]=MAX(Date Table[WeekRank])-1))

In this table, I have both a Week Rank and Week Number column:

WeekRank = RANKX(ALL(Date Table),Date Table[WkStartDate],,ASC,Dense)

WeekNum = WEEKNUM('Date Table'[WkStartDate])

WkStartDate = Date Table[Business_Date] - WEEKDAY('Date Table'[Business_Date],2)+1

WkEndDate = Date Table[Business_Date] -WEEKDAY(Date Table[Business_Date],2)+7

I have 3 business segments I'm looking. And they have their own dimension table. I'll call them:

Books

Shoes

Candles

If I'm just looking at a roll up, prior week calc works fine. But when I bring in the segments, the rollup just duplicates under each segment. What am I missing?? 

[danextian · Accepted solution]

Hi @Tihannah,

I've tried recreating your use case usnig my dummy data and it seems that your segment table doesn't have a relationship with your fact/sales table. You can see in the screenshot below that prior week is split into segments

Without the relationship, you'll see equal amounts for each segment by week.

Please see attached pbix for your reference. 

[Tihannah]

My segment table did have a relationship to the fact table, but the attached file helped me figure out where I was going wrong with the week columns and my dax, so thank you!