Drill down is not working

[rajanimaddala]

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

[v-lid-msft]

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]

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

[rajanimaddala]

Sorry, just a correction to the TotalSales_LY formula.

*SAMEPERIODLASTYEAR(valendar[Date])