Forum Discussion

Tihannah's avatar
Tihannah
Resolver II
3 years ago
Solved

Prior Week Calculation with Segments

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?? 
 
 
Need Help
  • danextian's avatar
    danextian
    3 years ago

    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. 

2 Replies

  • danextian's avatar
    danextian
    Super User
    3 years ago

    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's avatar
      Tihannah
      Resolver II
      3 years ago

      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!