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] ) )
I've noticed the model involved can make a big difference in which approach works best and I don't fully understand why yet, so I'm attaching another (much bigger) model I've been tinkering with for additional comparisons.
AlexisOlson Big difference I see is that the visual doesn't go to the Date granularity, which means it isn't the same scenario.
- AlexisOlson2 years ago
Super User
Good point on the granularity. Even matching granularity though, there are still different behaviors between files that I don't understand (in particular, TOTALYTD performs similarly to your measure in Contoso).
- lbendlin2 years ago
Super User
In general you don't want to include the syntax sugar in your tests. Keep to the basic functions.
- Greg_Deckler2 years ago
Community Champion
AlexisOlson I'll have to look closer but both have a data table marked as a date table. The Dates table in Contoso though has significantly less rows in it. Triple in fact.
- ValtteriN2 years ago
Community Champion
Greg_Deckler while it is quite clear that no calculate is often superior in terms of performance. I tend to agree with lbendlin that in most cases basic functions are the better choice strictly because your co-developers might not understand why you are using this kind of fancy dax instead of basic structures. I would clasisfy this kind of dax as legacy code since it is unreasonably hard to understand when compared to what it achieves.