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 I have been working through each one. Currently I am having issues with Previous Week. I have attached my version of your code technique for previous week. The code below is an order of magnitude worse than the No CALCULATE version so can't accept it as a solution to the problem. Yes, it is orders of magnitude faster than the other No CALCULATE approaches but it's still almost a full second for the DAX query versus 20 ms. My testing PBIX is attached below signature.
Alexis Olsen (PW) =
VAR _AllDates_ = SUMMARIZE ( ALL ( FactInternetSales ), Dates[Date] )
VAR _MinDate = MIN( 'Dates'[Prior Week Date] )
VAR _MaxDate = MAX( 'Dates'[Prior Week Date] )
VAR _DateRange_ =
FILTER (
_ALLDates_,
'Dates'[Date] >= _MinDate &&
Dates[Date] <= _MaxDate
)
VAR _Result = CALCULATE ( [Internet Sales], _DateRange_ )
RETURN
_Result
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] )
)- Anonymous2 years agoNot applicable
AlexisOlson & Greg_Deckler - I see this works faster, but prior year calculation don't solve the Leap Year issue.
- Greg_Deckler2 years ago
Community Champion
Anonymous I might be wrong, but I feel like the leap year issue is a problem for all approaches and people have different ways that they choose to solve/approach that problem. Good catch but I really wasn't focused on leap year stuff.
- Greg_Deckler2 years ago
Community Champion
AlexisOlson Very nice!! I am marking your answers as solutions. Also, I have attached my testing file to the original message that contains all of the measures so that other people can easily test as well.