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] ) )
AlexisOlson Big difference I see is that the visual doesn't go to the Date granularity, which means it isn't the same scenario.
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.
- Greg_Deckler2 years ago
Community Champion
ValtteriN I think you need to review the CALCULATE partial-solution to this problem and then tell me which one is more intuitive for someone to understand. Hint, they are literally the same solution except that one uses SUMX at the end and the other CALCULATE. The SUMX directly sums the value in the virtual table created. The CALCULATE applies the virtual table created as a filter of the base table over which a measure is applied.
Now, on the opinion of which of those is more intuitive to understand and debug, well, I have my opinion.