Forum Discussion
Community ChampionCALCULATE Challenge - Round 1
I think this should qualify, though it isn't super clean.
VAR _AllDates_ = SUMMARIZE ( ALL ( FactInternetSales ), Dates[Year], Dates[Date] ) VAR _DateRange_ = WINDOW ( 1, ABS, 0, REL, _AllDates_, ORDERBY ( Dates[Date] ), PARTITIONBY ( Dates[Year] ) ) VAR _Result = CALCULATE ( [Internet Sales], _DateRange_ ) RETURN _Resultthank God it's such an obvious and easy solution that even a DAX novice could understand and doesn't rely on a function introduced within that last year which pretty much means the solution was unsolvable for 7 years or so...
WINDOW isn't required. A plain filter works fine too and has been around since the beginning.
VAR _AllDates_ = SUMMARIZE ( ALL ( FactInternetSales ), Dates[Date] ) VAR _CurrDate = MAX ( Dates[Date] ) VAR _CurrYear = YEAR ( _CurrDate ) VAR _DateRange_ = FILTER ( _ALLDates_, YEAR ( Dates[Date] ) = _CurrYear && Dates[Date] <= _CurrDate ) VAR _Result = CALCULATE ( [Internet Sales], _DateRange_ ) RETURN _ResultHowever, there are still 15 other date intelligence measures in the file that are part of the challenge.
I'm not really interested in doing all 15 other ones as I don't think they'll reveal much that this YTD example doesn't already. If there is one that you think is meaningfully different, let me know.
Greg_Deckler, PW is an easy case where the TI solution or something like the following is just as fast.
CALCULATE ( [Internet Sales], TREATAS ( VALUES ( Dates[Prior Week Date] ), Dates[Date] ) )
Community Champion
Super UserI think this should qualify, though it isn't super clean.
VAR _AllDates_ =
SUMMARIZE (
ALL ( FactInternetSales ),
Dates[Year],
Dates[Date]
)
VAR _DateRange_ =
WINDOW (
1, ABS,
0, REL,
_AllDates_,
ORDERBY ( Dates[Date] ),
PARTITIONBY ( Dates[Year] )
)
VAR _Result = CALCULATE ( [Internet Sales], _DateRange_ )
RETURN
_Result- tamerj1
What the formula engine is trying to do is to create the following table:
Date Date YTD Sum of SalesAmount Then it does the aggregation over Date.
With help of some mathmatics, given that the Date table is 7,670 rows the formula engine would produce a crossjoin table of around (1/2 )* (7,670) * (7,670) = 29,414,450 rows. This is simply the area of a triangle.
In opttimized versions, the corossjoin is happening to only the dates that do exist in the fact tabe (1,124) rows. That would result in a table of around (1/2 )* (1,124 * (1,124) = 6,316,680 rows.
This is why the cardinality of the Dates[Date] and FactInternetSales[Order Date] do matter alot.
Usually I use the Window function following a NoCALCULATE approach by pre-calculating values then pushing all other calculation to the formula engine. For example the following produces the simplist and fastest query plan along with the minimum number of storage engine queries among all other solutions.Internet Sales (YTD) Window = VAR AllDateSales = SUMMARIZE( ALL ( FactInternetSales ), Dates[Year], Dates[Date], "@Amount", SUM ( FactInternetSales[SalesAmount] ) ) VAR Result = SUMX ( WINDOW ( 0, ABS, 0, REL, AllDateSales, ORDERBY ( Dates[Date] ), PARTITIONBY ( Dates[Year] ) ), [@Amount] ) RETURN ResultI would however say that your WINDOW-CALCULATE solution matches the performance of other NoCALCULATE solutions so I vote for it as an acceptable solution.
- Greg_Deckler
AlexisOlson I'll have a look in the morning. But if it does work, I mean, thank God it's such an obvious and easy solution that even a DAX novice could understand and doesn't rely on a function introduced within that last year which pretty much means the solution was unsolvable for 7 years or so...
- Greg_Deckler
AlexisOlson In my testing looking at the DAX query in Performance Analyzer this seems consistently about 40%-50% slower than the fastes No CALCULATE approach. But, we're quibbling over 10's of ms here so I feel like this passes. However, there are still 15 other date intelligence measures in the file that are part of the challenge.
