Forum Discussion
Similar period with variable year interval
latimert Well, you can try this:
Measure = VAR __Start = MIN('Table'[Date]) VAR __PreCOVIDStart = IF(MONTH(__Start) > 2, DATE(2019,MONTH(__Start),1), DATE(2020,MONTH(__Start),1) ) VAR __PreCOVIDEnd = EOMONTH(__PreCOVIDStart,0) VAR __SalesPC = CALCULATE(SUM('Table'[Sales]),ALL('Table'),'Table'[Date]>= __PreCOVIDStart && [Date]<=__PreCOVIDEnd) RETURN __SalesPCFiltering ranges sucks and using CALCULATE is hit or miss depending on your visual configuration and whether or not you have a star schema as well as what cycle the moon happens to be in (generally works during a Waning Gibbous).
Greg_Deckler May have been a bit too keen haha.
This measure works, however, it is extremely slow to load into a table of 6 rows (July - December). Is it expected that this would be slow? The dataset isn't huge and most measures are instantaneous.
If you have any tips to make this faster, that would be much appreciated.
Community Championlatimert Well, you can try this:
Measure =
VAR __Start = MIN('Table'[Date])
VAR __PreCOVIDStart = IF(MONTH(__Start) > 2, DATE(2019,MONTH(__Start),1), DATE(2020,MONTH(__Start),1) )
VAR __PreCOVIDEnd = EOMONTH(__PreCOVIDStart,0)
VAR __SalesPC = CALCULATE(SUM('Table'[Sales]),ALL('Table'),'Table'[Date]>= __PreCOVIDStart && [Date]<=__PreCOVIDEnd)
RETURN
__SalesPCFiltering ranges sucks and using CALCULATE is hit or miss depending on your visual configuration and whether or not you have a star schema as well as what cycle the moon happens to be in (generally works during a Waning Gibbous).
Will just tell the recipient to wait for the next waning gibbous then!
Jokes aside, calculate was significantly faster but I'll keep an eye out for any inconsistencies.
This has been very helpful. Thank you very much!
- Greg_Deckler
latimert I've got a lot of problems with that function, but it's useful when it's useful! Festivus!