Forum Discussion
No CACULATE challenge
- 3 years ago
Hi AlexisOlson & Greg_Deckler
This one uses neither CALCULATE nor CALCULATETABLE and performs faster than the original oneSales Amount (Delivered) 2 = VAR SelectedSales = ALLSELECTED ( Sales ) VAR SummarySales = SUMMARIZE ( SelectedSales, Sales[DeliveredDateKey], "@SalesAnount", SUM ( Sales[SalesAmount] ) ) RETURN SUMX ( VALUES ( 'Calendar'[DateKey] ), VAR FilteredSales = FILTER ( SummarySales, [DeliveredDateKey] = 'Calendar'[DateKey] ) RETURN SUMX ( FilteredSales, [@SalesAnount] ) ) - 3 years ago
AlexisOlson Good challenge! I am a Pro CALCULATE but recommend users not to stick with a single side.
Here are my solutions:Solution 1 using INNERJOIN, performs in 40ms without any filter
Delivered Amount AS = VAR Dates = SELECTCOLUMNS ( VALUES ( 'Calendar'[DateKey] ), "DeliveredDateKey", 'Calendar'[DateKey] & "" ) VAR SalesByDelivery = SELECTCOLUMNS ( SUMMARIZE ( ALLSELECTED ( Sales ), Sales[DeliveredDateKey], "Sum", SUM ( Sales[SalesAmount] ) ), "DeliveredDateKey", [DeliveredDateKey] & "", "Sales", [Sum] ) VAR Result = SUMX ( NATURALINNERJOIN ( Dates, SalesByDelivery ), [Sales] ) RETURN ResultSolution 2 using CONTAINSROW or the IN performs in 23 ms
Delivered Amount AS 2 = VAR SalesByDeliveryDate = SUMMARIZE ( ALL ( Sales ), Sales[DeliveredDateKey], "TotalSales", SUM ( Sales[SalesAmount] ) ) VAR CurrentYearRows = FILTER ( SalesByDeliveryDate, CONTAINSROW ( VALUES ( 'Calendar'[DateKey] ), Sales[DeliveredDateKey] ) ) VAR Result = SUMX ( CurrentYearRows, [TotalSales] ) RETURN ResultCumulative Total - I added a YearMonth column in the Dates table, since we are not showing date level I decided to reduce granularity, takes about 50ms.
CT DeliveredAmount AS = VAR SalesByDelivery = SUMMARIZE ( ALL ( Sales ), Sales[DeliveredDateKey], "@TotalSales", SUM ( Sales[SalesAmount] ), "@YearMonth", YEAR ( Sales[DeliveredDateKey] ) * 100 + MONTH ( Sales[DeliveredDateKey] ) ) VAR Result = SUMX ( FILTER ( SalesByDelivery, [@YearMonth] <= MAX ( 'Calendar'[YearMonth] ) ), [@TotalSales] ) RETURN Resulttamerj1 I tried and your code and it returns incorrect subtotals.
I'm awarding the win to tamerj1 for the original problem. It's about 20ms in testing on my machine instead of 10ms for mine but that's still super fast.
I'm awarding the win to AntrikshSharma for the cumulative challenge since that solution works for subtotals as well.