Forum Discussion
CALCULATE Challenge - Round 1
- 2 years ago
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 _Result - 2 years ago
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...
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.
- 2 years ago
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] ) )
There seems to be something pathological going on that's making the query planner do weird things with simpler measures. I'd really like to figure out what as I think that knowledge may be broadly applicable in measure optimization. It should not be doing this (some sort of cross-join?).
I got the above when using this code:
VAR _CurrYear = SELECTEDVALUE ( Dates[Year] )
VAR _CurrDate = SELECTEDVALUE ( Dates[Date] )
VAR _Result =
CALCULATE (
[Internet Sales],
Dates[Year] = _CurrYear,
Dates[Date] <= _CurrDate
)
RETURN
_Result
Using WINDOW is better but it still has steps with 1.4 million records.
CALCULATE (
[Internet Sales],
WINDOW (
1, ABS,
0, REL,
ORDERBY ( Dates[Year], ASC, Dates[Date], ASC ),
PARTITIONBY ( Dates[Year] )
)
)
Can any DAX masters like marcorusso or jeffrey_wang shed some light?
AlexisOlson
Exactly.
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.