Forum Discussion

rajanimaddala's avatar
rajanimaddala
Icon for Helper II rankHelper II
6 years ago

Drill down is not working

Hi,

I have calendar table and fact table. Both are related as fact.TransactionDate -> Calendar.Date

 

I have Week column in calendar table in the format Apr Week 1, Apr Week 2

 

Created measures

TotalSales = SUMX(FILTER(fact, [source] = "Sales"), [Sales Amount])

TotalSales_LY = CALCULATE([TotalSales], SAMEPERIODLASTYEAR(calendar[Date], -1, YEAR))

 

I have placed table visual and dragged [Week], [ProductGroup], TotalSales, TotalSales_LY

Perfectly working.

I have changed Table to Clustered column chart.

TotalSales is fine. TotalSales_LY is not showing correct result.

TotalSales_LY for week 1 is showing correct. TotalSales_LY for week 2 is showing as Week 1 value + week 2 value and vice versa

Why is it adding all weeks values cumulatively?

Wehn I drill down also the same issue. Adding entire months value for each productgroup even though I right click and drill down on Week 1

 

What is the issue?

 

Thanks in advance

 

Need Help

3 Replies

  • rajanimaddala's avatar
    rajanimaddala
    Icon for Helper II rankHelper II
    6 years ago

    Sorry, just a correction to the TotalSales_LY formula.

     

    *SAMEPERIODLASTYEAR(valendar[Date])

     

  • v-lid-msft's avatar
    v-lid-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi rajanimaddala ,

     

    Sorry for that, but we cannot reproduce your issue after  applying your formula on our sample table.

     

     

     

    Does your chart similar with the sample we have shared? What is the relationship between your ‘fact’ table and ‘calendar’ table, “Many to one” or “one to one”? Does the calendar table only contain Continuous time value?

     

    Could you please reproduce this issue on the sample we have shared?

     

    BTW, pbix as attached.

     

    Best regards,

     

    • rajanimaddala's avatar
      rajanimaddala
      Icon for Helper II rankHelper II
      6 years ago

      Hi v-lid-msft,

       

      I have exactly same relationship as in your pbix

       

      Please see attached 2 screenshots

      1. Week in Fact table which is correct

      2. Week in calendar ta ble which is wrong

       

      Week calculation is as below

      CASE

      WHEN DATEPART(DAY, [transaction Date]) BETWEEN 1 AND 7 THEN 'Week 1'

      WHEN DATEPART(DAY, [transaction Date]) BETWEEN 8 AND 14 THEN 'Week 2'

      WHEN DATEPART(DAY, [transaction Date]) BETWEEN 15 AND 21 THEN 'Week 3'

      WHEN DATEPART(DAY, [transaction Date]) BETWEEN 22 AND 28 THEN 'Week 4'

      ELSE 'Week 5'

      END

       

      Why is not working with Week in calendar tableWeek in Fact TableWeek in Calendar Table