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] ) )
Super UserThe other interesting thing about this approach is that you are pretty much destroying the star schema or at least not leveraging it in the slightest. You effectively create a new table that includes all rows in the fact table summarized by Date and then create a filter clause over that. So this kind of says to me that the mantra of the star schema coupled with CALCULATE as being the silver bullet for all things DAX is effectively nonsense because you basically have to blow-up the star schema to get CALCULATE to function at any reasonable performance level.Finally, I don't think it's intuitive that the SUMMARIZEd table includes all dates in it considering the filter context within a row within the table is to a specific date. That seems weird to me although I imagine it is because using SUMMARIZE against ALL of the fact table and then by Date brings all of those dates back into context somehow versus the individual date at the visual row.
The SUMMARIZE works on the expanded fact table, which leverages the star schema. I find using SUMMARIZE this way useful in many situations.
This situation where you have a visual with thousands of rows does indeed seem to be a pathological case for basic CALCULATE patterns. There are other situations where those same patterns will outperform the No CALCULATE approach in the post (see the Contoso example I uploaded, for example). I'd expect Tamer's WINDOW solution to work well broadly.
Super UserDon't forget that the star schema is mostly smoke and mirrors, and that behind the scenes everything is based on expanded tables anyway.